Excel Conditional Formatting Bizarre Behavior

Question

I have a really simple conditional formatting. Using the formula =g2=h2, I want to change the color of rows where g=h. Can't get much simpler than this.

The conditional formatting is not coloring random cells. The formula works for the most part, but - randomly - the cell in column E or C, etc. won't be colored. Every other cell in the row will be colored.

More odd, if I delete the rule and add the rule again, the cells that are not coloring change. Where cell C might be missing the color the first time, the second time cell E might be missing the color.

This is the most maddening, nonsensical, behavior I have ever seen out of Excel. Any ideas?

Answer 1

You need to understand how conditional formatting that uses formulas is related to the current cell. Then you need to understand the difference between absolute and relative references.

When you create a rule with the formula =g2=h2 it is very important which cell is the active cell when the rule is created.

If G2 is the active cell, then the rule will be evaluated like this: If my value is the same as the value of the cell in the next column, same row, then use the format.

If H2 is the active cell, then the rule will be evaluated like this: If my value is the same as the value of the cell in the previous column, same row, then use the format.

If A1 is the active cell, then the rule will be evaluated like this: If the value of a cell in the next row and 6 columns to the right is the same as the value of the cell in the next row and 7 columns to the right, then use the format.

With the last example, you can see that the formula does not necessarily need to reference the current cell. If you understand that, you can exploit conditional formatting in new ways. If you don't understand what's happening, it's simply confusing.

Typically, when you reference row 2 in your formula, you probably want to format some cells in row 2 and need to make sure that you select the correct row. Also, If you want to change the color of a row, based on the values in columns G and H, you need to use absolute references, like this

=$G2=$H2

This reference will always look at cells in columns G and H. The row number will be evaluated based on the current cell. Typically, you would select A2 to Z2 or similar, then add the rule with the formula above. You can then apply the rule to other rows or just copy and paste the format to other rows.

Note that applying conditional formatting to whole rows or columns is not advisable, as it will slow down the workbook.