Conditional Formatting

[deemer98]

Good afternoon all,

I want cell D4 to turn red if the number entered into it is less that cell C4 and to turn green if it is => than C4. I do not wnat it to be red when there is nothing in the cell. I'm struggling. Thanks in advance!

David

 

[JoeMo]

Use a formula to set the condition. Here's the formula for green fill:

=AND(NOT(ISBLANK(C4)),NOT(ISBLANK(D4)),D4>=C4)

You can adapt this for the red fill.

 

[deemer98]

Very close Joe. Thanks! It was probably me not being clear enough in my ask...

C4 will ALWAYS be populated. D4-J4 need to turn red or green depending on if they are less than C4(red) or greater than or equal to c4(green). The way your formula is now, for some reason, D4 has to be poplulated before E4 wil work correctly and so on

 

[shobolos]

Very close Joe. Thanks! It was probably me not being clear enough in my ask...

C4 will ALWAYS be populated. D4-J4 need to turn red or green depending on if they are less than C4(red) or greater than or equal to c4(green). The way your formula is now, for some reason, D4 has to be poplulated before E4 wil work correctly and so on

I believe you'll need 2 conditional formatting rules -

- D4-J4 need to turn red if they are less than C4: =D4<$C4

- D4-J4 need to turn green if they are greater than or equal to c4: =D4>=$C4

 

[deemer98]

Sorry Shobolos but this isn't working. Any other thoughts? Joe's reply is very very close to working

 

[JoeMo]

Very close Joe. Thanks! It was probably me not being clear enough in my ask...

C4 will ALWAYS be populated. D4-J4 need to turn red or green depending on if they are less than C4(red) or greater than or equal to c4(green). The way your formula is now, for some reason, D4 has to be poplulated before E4 wil work correctly and so on

Agreed !

Do this:
1. Select D4:J4.
2. Home>Conditional Formatting >New Condition>Use a formula
Formula for green fill is:

=AND(NOT(ISBLANK(D$4)),D$4>=$C$4)

Similar approach for red fill.