Conditional Formatting using AND and INDIRECT

[mumbauer]

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.

 

[mumbauer]

Ok, so it should be easy to duplicate this...

New worksheet and starting at A1 put in -1 then go to A2 (and more if you want) and put in positive numbers.

Highlight columns A and do conditional formatting.

The formula is =INDIRECT("A"&ROW())<=0

Change the formatting to put in "fill" (background color). You should now see Cell A1 and any empty cell filled with that color.

Now change the formula by wrapping AND() around it like so:
=AND(INDIRECT("A"&ROW())<=0)

It will no longer do the formatting.

 

[mumbauer]

Oh...but if you use =AND(A1<=0) then it will work again!

 

[T. Valko]

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.

Hmmm...

I can't explain why that doesn't work. It seems like it should.

This worked for me:

=(MOD(ROW()-3,49)=38)*(INDIRECT("B"&ROW()-4)>=0)

 

[mumbauer]

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

 

[T. Valko]

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

You're welcome. Thanks for the feedback!

 

[gdooling]

Thank you from 2015! I was trying the same thing and I couldn't figure out why they each worked separately, but not together!