Are Excel's Conditional Formatting Formulas Broken

Question

Ok, so I am using excel 2007, and am trying to do some fairly complicated conditional formatting. Consider the following:

=1=1

Highlights the cell.

=INDIRECT(ADDRESS(2,COLUMN()))=5

Highlights cells in a column where the second row has 5 in it.

=AND(1=1,2=2)

Highlights the cell.

=AND(INDIRECT(ADDRESS(2,COLUMN()))=5,1=1)

Highlights nothing?

It seems that any complicated things I can do simply don't work. Even when the expression evaluates true when put into the cell directly, it doesn't always make the formatting work correctly. Has anybody else experienced this? Anybody have a workaround?

Answer 1

I get the same results on Excel 2003. Even your second example (which is missing the "=" BTW) has strange behaviour, since not all the cells in the column are highlighted until I minimize and restore the window.

I was able to get it to work by defining a name "foo" as "=INDIRECT(ADDRESS(2,COLUMN())" and then using "=AND(foo=5,1=1)" for the condition.

To define a name, use Insert->Name->Define, type "foo" (or whatever) in the Name box, and the formula in the Refers To box. Normally a named range refers to a fixed cell, but it can also refer to a constant, a formula, a range, etc. See Chip Pearson's site for details.