cell color formatting based on values

[hotdogs1999]

Hello,

i'm after a way to change a cells color based on the value of that cell in relation to the value in an adjoining cell. essentially what the actual value is in relation to the target value.
if the actual value cell is lower than the target value cell then i want it to turn red, and green if its higher than or equal to.

i realise that conditional formatting can be used for this. it works ok for the red option, but when it comes to choosing greater than or equal to on the green option it makes the cell green even if the target value cell is empty (because its equal to the nil value).

is there a better way of doing this?

 

[kweaver]

Have you tried making the green rule:

=AND(A1>=B1,B1<>"")

 

[Marcelo Branco]

You can use a formula in Conditional Formatting with an AND condition.

Assuming A1 is the cell of interest and B1 is the target, this should work
=AND(A1<>"",B1<>"",A1>=B1)

M.

 

[hotdogs1999]

Thanks, that works great