Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

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)

share|improve this answer
    
I'll try your suggestion –  Charles Mar 26 '10 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 '10 at 9:56
    
Wow this actually works! How come row numbers get recalculated? –  Robert Koritnik Aug 28 '10 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() –  Rhys Gibson Aug 30 '10 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? –  Robert Koritnik Aug 31 '10 at 14:46

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.