Conditional function row formatting based on columns values being




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




Jacob Skaria

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 Format>Conditional Formatting>
3. For Condition1>Select 'Formula Is' and enter the below formula


4. Click Format Button>Pattern and select your color
5. Hit OK

If this post helps click Yes



Bernard Liengme

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

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

Note that I am assuming your selection started in row 1. If it starts in say
row 5, then use

best wishes

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question