Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional function row formatting based on columns values being
Hi,
I am evaluating financial data based on payments received for services or written off. The values in column I is the charged amount the values in j-l are either payments or write off amounts. The values in I are positive the values in j-l are either 0 or negative. I am trying to cause a row to change color if the "absolute" values in i2 and l2 are the same (in other words we wrote off the full amount) and then apply it to the rest of the sheet. I have selected the whole sheet and done a CF using the formula =ABS(i=l) with no luck I have also tried =if(i=l) with no luck and various variations thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional function row formatting based on columns values being
1. Select the cell/Range (say A2:J10). Please note that the cell reference
mentioned in the formula is in the 2nd row which contains the active cell in the selection. Active cell will have a white background even after selection 2. From menu FormatConditional Formatting 3. For Condition1Select 'Formula Is' and enter the below formula =AND($I2<"",ABS($I2)=ABS($J2)) 4. Click Format ButtonPattern and select your color 5. Hit OK If this post helps click Yes --------------- Jacob Skaria "medic" wrote: Hi, I am evaluating financial data based on payments received for services or written off. The values in column I is the charged amount the values in j-l are either payments or write off amounts. The values in I are positive the values in j-l are either 0 or negative. I am trying to cause a row to change color if the "absolute" values in i2 and l2 are the same (in other words we wrote off the full amount) and then apply it to the rest of the sheet. I have selected the whole sheet and done a CF using the formula =ABS(i=l) with no luck I have also tried =if(i=l) with no luck and various variations thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional function row formatting based on columns values being
I am having trouble reading you message - the I and 1 seem confused
So I will use column D and E for clarity D is a number always positive (like you I column) , E a number than can be positive or negative (like your J) Since only J can have either sign, it makes sense to take the abs of this only Select all the data; open Conditional Formatting dialog Formula IS =ABS($E1)=$D1 Set the format required If you want to avoid this applying the rows with no values in the two columns =AND(COUNT($D1:$E1)=2,ABS($E1)=$D1) Note that I am assuming your selection started in row 1. If it starts in say row 5, then use =AND(COUNT($D5:$E5)=2,ABS($E5)=$D5) best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "medic" wrote in message ... Hi, I am evaluating financial data based on payments received for services or written off. The values in column I is the charged amount the values in j-l are either payments or write off amounts. The values in I are positive the values in j-l are either 0 or negative. I am trying to cause a row to change color if the "absolute" values in i2 and l2 are the same (in other words we wrote off the full amount) and then apply it to the rest of the sheet. I have selected the whole sheet and done a CF using the formula =ABS(i=l) with no luck I have also tried =if(i=l) with no luck and various variations thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting based on values from other sheet | Excel Worksheet Functions | |||
Conditional formatting based on max in range of other columns | Excel Worksheet Functions | |||
Conditional Formatting based on top values | Excel Worksheet Functions | |||
Conditional Formatting in Pivot Based on other values in pivot. XL | Excel Discussion (Misc queries) | |||
Conditional values based on formatting? | Excel Worksheet Functions |