It appears that Excel 2010's conditional formatting is broken. I'm trying to format a range of cells using a formula. If I set conditional formatting to highlight a single cell, it works fine. However, if I try to format a *range* of cells using a single rule (again, the rule is using a formula) then only the top-left cell of the range works correctly. All of the other cells get the chosen formatting, but they never lose it. That is, the chosen highlighting never goes off for the range (except for the top-left cell, which works correctly). Has anyone else seen this or know of a way to get this working correctly?

Conditional formatting works fine in Excel 2010.

Make sure that the formula uses relative cell addresses where needed.

For example, if you want to highlight cells in A2:K50 if the value of the cell in column D in the same row is larger than 100, use

=$D2>100

as formula. The column reference $D is absolute because we want to look at column D, but the row reference 2 is relative because we want Excel to adjust it automatically for the cells in other rows.

And make sure that the rule is applied to the correct range.

Hi,

Just checking in to see if the information of Hans was helpful. Please let us know if you would like further assistance.

And also please refer to Use a formula to determine which cells to format of the following link to check whether the steps you applied the conditional formatting is correct.

http://office.microsoft.com/en-us/excel-help/add-change-find-or-clear-conditional-formats-HP010342157.aspx#BM10

Hans is correct [of course] in his answer. The reason for this seemingly un-intuitive behavior is as follows.

Suppose, as Hans suggested, you want to conditionally format each row in a range of cells A2:K50 based on the TRUE/FALSE value in column D of each row. If you use the formula "= D2 = TRUE" as the basis for your conditional formatting, for example, then the relative addressing for both row and column will apply the conditional formatting as follows:

D2(TRUE/FALSE) > A2 formatting

E2(TRUE/FALSE) > A3 formatting

F2(TRUE/FALSE) > A4 formatting

...

Thus, only the left-most cell of each row, A2 above, (and the "top-left cell" of the entire range) is conditionally formatted based on the value in the D-column. The other cells in the row/range are conditionally formatted based on the TRUE/FALSE values in columns E through N. If you don't have TRUE/FALSE values in columns E through N, then no conditional formatting will be applied for the non-"left-most cell" in that row.

 

It's squirrly. Sometimes it works, other times it doesn't for no apparent reason, even though the rules are applied the same way.

What would *really* be nice would be the ability to save a rule and turn on and off as needed. Would be a big help in finding differences in columnar data.

Right now you have define the rule, find the difference, then delete the rule, reselect the data, and re-input the rule the find the next difference.

Works, but VERY unwieldy!

I realize I'm late to the party, Jwil52, but perhaps you could work around your issue of turning on and off formatting rules?

What if you make the rule and AND rule, where the first condition is your original rule, and the second condition simply reads a given cell on a given sheet. Then you can use that cell to input a 1/0/TRUE/FALSE to in effect toggle your original rule on and off.