Conditional Formatting

deemer98

Board Regular
Joined
Sep 9, 2005
Messages
128
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
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Use a formula to set the condition. Here's the formula for green fill:

Code:
=AND(NOT(ISBLANK(C4)),NOT(ISBLANK(D4)),D4>=C4)
You can adapt this for the red fill.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Sorry Shobolos but this isn't working. Any other thoughts? Joe's reply is very very close to working
 
Upvote 0
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:
Code:
=AND(NOT(ISBLANK(D$4)),D$4>=$C$4)
Similar approach for red fill.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top