# Conditional Formatting based on a formula.

#### DarbyBrown

##### New Member
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.

### Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

#### GraH

##### Well-known Member
Perhaps like this:
1. select cell D4
2. Formula in CF is = (\$B4-\$C4)<0
3. Apply the range \$D\$4:\$D\$13

#### DarbyBrown

##### New Member
Tried your suggestion, still didn't work

#### DarbyBrown

##### New Member

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

##### MrExcel MVP, Moderator
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

##### New Member

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

##### MrExcel MVP, Moderator
Glad we could help & thanks for the feedback.

#### DarbyBrown

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

Replies
2
Views
67
Replies
3
Views
95
Replies
7
Views
75
Replies
3
Views
62
Replies
0
Views
86