Conditional Formatting based on a formula.

[DarbyBrown]

How do I use Conditional Formatting to change the background color of a columns cell depending on if the difference of two other cells, in that same row, produces a negative value?

i.e.: If Bx-Cx is less than 0 change the background color of Dx to Pink (as in Row 9 below)

Dx has a formula

=If(Cx>Bx, 0, Bx-Cx)
If Cx is greater than Bx, display a 0 in Dx, otherwise display the value of Bx – Cx in Dx.
I total the values of column D and need the negative numbers to be counted as 0. (If that makes any difference.)
That is why I cannot use the value of Dx to base my conditional formatting on.

Also, the values in C and B are linked from cells on other sheets in the workbook. So, I have also tried
If(Val(Cx)>Val(Bx)),
that did not work either!

(Totals are the sum of values from rows 5 thru 13)

Any assistance will be greatly appreciated.

 

[GraH]

Perhaps like this:

1. select cell D4
2. Formula in CF is = ($B4-$C4)<0
3. Apply the range $D$4:$D$13

 

[DarbyBrown]

Tried your suggestion, still didn't work

 

[petertenthije]

Tried your suggestion, still didn't workView attachment 13307

You should remove the quotation marks from the formula.

 

[DarbyBrown]

Not sure where they came from, I didn't put them there. Any it worked except, I entered the greater than instead of less than symbol. Changed that and it worked as I want. Thank You!!

To add to the situation, is there a way that if I roll the mouse over the D? cell that it will display the negative number hidden by the 0

 

[Fluff]

Why not use in D4
=B4-C4
and in D15
=SUMIF(D4:D13,">0")
then you can use the CF to display a 0 is the value is -ve, but if you select the cell you can see the true value.

 

[DarbyBrown]

Didn't really need to hide the value in D4 behind a 0. The SUMIF formula took care of the problem. I didn't care if the negative number showed, I just didn't want it included in the total. SUMIF never entered my mind, shame on me.... Thank you very much Fluff!!!!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[DarbyBrown]

Thank You also petertenthije you contributed to my re-education also. I appreciate the help!!