conditional Formatting help

[JayM]

I have put some conditional formatting on columns in a spreadsheet as folows:

Cell Value is less than =TODAY() - format cell to Red
Cell Value is less than =TODAY()+30 - format cell to Orange
Cell Value is greater than =TODAY() - format cell to Green

But I now want to say if the cell is blank to leave it with no colour

Any help would be greatly appreciated.

JayM

 

[John C]

Change drop down box to 'Formula is' instead of Cell Value
=AND(A2<>"",A2<TODAY()) for red
=AND(A2<>"",A2<TODAY()+30) for orange
=AND(A2<>"",A2>TODAY()) for green

Note: Select range(s) that will have this conditional formatting. Delete the
current conditions. Whichever cell is the 'active' cell in the selected
range(s) (it will be the one that if you typed the typing would go there),
use that cell for the A2 in my above examples. Then, so long as you have the
entire range(s) selected, the conditional formatting will be applied to each
cell, and adjusting for rows and columns.

Note2: If the date in the cell is 15 days in the future, you may want to be
sure you have the order in which you check for the conditions in the order
you desire, because it would be true for both Orange AND Green.

Hope this helps.

 

[JayM]

Thanks for your help changed things slightly now and I get the results I want:

=AND($P1<>"",$P1<TODAY()) for red
=AND($P1<>"",$P!<TODAY()+30) for orange
=AND($P1<>"",$P1>TODAY()+30) for green

however I will apply similar formatting to columns Q, R, S, T
in Column B I have a Surname I would like this to change to red if anyone of
the other mentioned columns turns red for that person. Hope that makes sense!