1

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?

0

1 Answer 1

2

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)

6
  • I'll try your suggestion
    – Charles
    Mar 26, 2010 at 17:51
  • Personally I would simply colour all alternate rows using the MOD conditional format, then re-colour the ones with numbers. So first condition is much simpler and colours every odd row red, say, then second formula colours ones with a number in column E green. This won't work if you need to be backwards compatible to older versions pre-2007, as it relies on not having to use the old "stop if true" way of operating rules.
    – AdamV
    Jun 6, 2010 at 9:56
  • Wow this actually works! How come row numbers get recalculated? Aug 28, 2010 at 23:20
  • It's not that row numbers get calculated, it's just that the only way to work out which row a cell is in is to use ROW() Aug 30, 2010 at 3:49
  • @Rhys: What I meant was: how come that $E1 forces the formula to re-evaluate itself? And how does it know that it has to change to a different row number? Aug 31, 2010 at 14:46

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .