Conditional Formatting based on a formula.

DarbyBrown

New Member
Joined
Jan 22, 2016
Messages
27
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!

conformat1.png

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

Any assistance will be greatly appreciated.
 

Some videos you may like

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
Joined
Mar 22, 2020
Messages
613
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
Joined
Jan 22, 2016
Messages
27

ADVERTISEMENT

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
Joined
Jun 12, 2014
Messages
48,365
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jan 22, 2016
Messages
27

ADVERTISEMENT

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
Joined
Jun 12, 2014
Messages
48,365
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,422
Messages
5,547,834
Members
410,813
Latest member
Vhinzvirgo
Top