Compare two Cells and highlight third cell

[Timmy]

I have a worksheet with cell A1 with a certain number and cell A2 with
another number in it. I would like to compare cells A1 vs A2.

If the value is less than A2, then the cell A3 should be highlighted in yellow
If the value is equal to A2, then highlight the cell A3 in green
If the value is higher than A2, highlight the cell A3 in red

Thanks in advance.

 

[David Biddulph]

Format/ Conditional Formatting/ Formula Is/ then complete as applicable, but
make sure you start your formula with the = sign, or else Excel often makes
some strange guesses as to what you intended.

 

[Timmy]

I tried your suggestion. It works for one condition at a time but not all
three.

Condition 1 Formula Is =$E:$E=$H:$H format light yellow
Condition 2 Formula Is =$E:$E<$H:$H format light bright yellow
Condition 2 Formula Is =$E:$E>$H:$H format light green

 

[Gord Dibben]

You must use just one cell for reference, not an entire column

Select E1:E30 and Format>CF>Condition1>Formula is:

=$E1=$H1 format to light yellow

Same for Conditions 2 and 3 with < and > operators.

Note the $ signs to lock the column but not the row.


Gord Dibben MS Excel MVP