Conditional Formatting using AND and INDIRECT

mumbauer said:

I'm not sure what's going on here but I'm trying to do conditional formatting that applies to an entire column (that way there are fewer conditional rules), but it only applies to a certain range of cells within that column. So what I did was setup the rule such as:

Applies To:
=$B:$Z

Formula I want to use:
=AND(MOD(ROW()-3,49)=38,INDIRECT("B"&ROW()-4)>=0)

If I copy the entire formula into a cell on the spreadsheet itself, it works just fine returning TRUE and FALSE depending on what row I'm on.

However, the above formatting rule does NOT format ANY row. But if I remove the AND function and the first condition (the one that limits which rows it's actually going to apply to) then it will work...OR if I do NOT use the INDIRECT function then it seems to work, it's some weird combonation of using the two together that is causing it to not evaluate to true.

Oh, and I thought of trying to get the formatting to work with the INDIRECT...then I put in the AND function but the only parameter was the original INDIRECT function (see below)...it stops formatting!
=AND(INDIRECT("B"&ROW()-4)>=0)

Office 2010 on Windows 7 64bit

If an example spreadsheet is needed, let me know and I'll try to come up with something.

Click to expand...

mumbauer said:

Ahhh, never would've thought of that!! Thanks so much, that works!

Click to expand...