excel - Conditional formatting: using row() inside indirect() inside and() doesn't work. What am I doing wrong?

Question

Say I have set a value of 55 on cell B2.

If it's value is 55, the cell's background color should be green. So I created a new rule using a formula by selecting "User a formula to determine which cells to format" and set the formula to the following:

=B2 = 55

It worked, obviously. But because we don't want to hard-code the row because we are using this spreadsheet as a template in an application that generates more rows by simply copying that cell to another row, I set the formula to this one:

=INDIRECT( "B" & row() ) = 55

And because we actually want two conditions and our requirement is not as simple as that, I tried the following:

=AND( INDIRECT( "B" & row() ) >= 50, INDIRECT( "B" & row() ) <= 60 )

Which means set the background to green if the value is between 50 and 60 (inclusive). And that did not work. But the following works:

=AND( INDIRECT( "B" & "2" ) >= 50, INDIRECT( "B" & "2" ) <= 60 )

Could anyone tell me why does the latter work but the former that calls row() doesn't?

It seems row() only doesn't work if it's used with combination of AND() and INDIRECT() as demonstrated below:

=AND( ROW() = 2 ) -> this one works
=INDIRECT( "B" & row() ) = 55 -> this one works too
=AND( INDIRECT( "B" & row() ) ) = 55 -> doesn't work :((

Could someone explain this why? Is this a bug in Excel 2010? And how do I solve this problem?

Answer 1

The behavior you faced looks like a bug, here are some forum links, where people describe exactly the same problem: link1,link2.

In one of those topics you can read that if you enclose your CF formula with ISERROR, it returns (for unknown reason) True. Same formula entered in a cell works perfectly fine.

The easiest solution in your case is to replace:

=AND(condition1,condition2)

With equivalent:

=(condition1)*(condition2)

The other workaround shown in one of the linked threads is to replace:

INDIRECT("B" & row())

With its equivalent:

INDEX($B:$B,ROW())

Both solutions work fine in conditional formatting rule.