Excel 2007 Conditional Formatting is not properly using custom formula provided

In Excel 2007, I want to conditionally color a row if it is odd numbered and then vary the coloring depending on if a specific cell (in column E) in that row contains a number (green) or empty(red). E.g. if E15 has a value of 2 and E13 has no entry, I would expect row 15 to be green and row 13 to be red.

My two formulas are:
To color red:
=IF((MOD(ROW(),2) = 1),NOT(ISNUMBER(INDIRECT("$E$"&ROW()))), FALSE)
To color green:
=IF((MOD(ROW(),2) = 1),ISNUMBER(INDIRECT("E"&ROW())), FALSE)

If I paste these formulas into cells on the worksheet I get the expected values. For row 15 the "red" equation is false and the "green" equation is true. For Row 13 the "red" equation is true and the "green equation is false. However if I use these formulas in the conditional formating use formula feature, all of my rows are red, any thoughts?

Your problem is INDIRECT. It is not playing nicely with your conditional formulas, which seems to be some sort of limitation around INDIRECT.

However, I don't think you need it. If I understand your requirement correctly, you can just change the green conditional formula to =IF((MOD(ROW(),2) = 1),ISNUMBER($E1), FALSE). The use of $E1 will force the formula to reevaluate for each row, so it turns into:

• =IF((MOD(ROW(),2) = 1), ISNUMBER($E1), FALSE) for E1
• =IF((MOD(ROW(),2) = 1), ISNUMBER($E2), FALSE) for E2
• =IF((MOD(ROW(),2) = 1), ISNUMBER($E3), FALSE) for R3
• etc.

Similarly, you can replace your red formula with =IF(MOD(ROW(),2) = 1,NOT(ISNUMBER("$E1)), FALSE)