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.

Answer 2

In Excel 2007, it would not accept my conditional format of

=AND(expr1, expr2)

where expr1 and expr2 were fairly complicated.

Just by adding an extra comparison, it started taking it.

=TRUE = AND(expr1, expr2)

I suspect it is validating for the presence of some basic operator, and it would not accept the condition without it.