Conditional Formatting Conflict Resolution

Last week I promised to talk about conflicts in conditional formatting.

A conflict in conditional formatting occurs when you have two rules that could apply to the same cell. For example, if rule1 applies if the cell value is greater than 3, and rule2 applies if the cell is greater than 6, then if the actual value is 7 or above then both rules could apply. Likewise, if one rule applies to everything greater than 0, and another applies to everything less than 5, then both rules could apply to everything between zero and five.

Some programs will assume that rule1 is the most important, and if it’s true then there’s no need to evaluate any other rules. If rule1 isn’t true, then it will check rule2, and if that’s not true then it will check rule3, and so on.

Other programs do the same thing, but in reverse. In this case, the last rule is more important than the first one. In effect, they let each later format override the previous one(s). Like painting a house, you only see the effects of the last color to go on. Even if rule1 is true, rule2 gets checked, and then rule3, at so on. If rule2 and rule3 are false, you see the format for rule1. But if rule2 is true and rule3 is false, you see the format for rule 2, whether or not rule 1 was true. Lastly, if rule3 is true, then it determines what you see, whether or not rule1 and rule2 are true.

Another permutation is that some programs assume that a rule which only sets a specific format for the background is simultaneously setting the default formats for the font and borders. Others, however, assume that a rule which only sets a specific format for one thing leaves the others open for changes.

For example, assume the following:
– Rule1 sets the font to blue if the value is greater than 3,
– Rule2 sets the background to red if the value is greater than 6,
– Rule 3 sets the background to yellow if the value is greater than 8,
– The value of the cell is 10.

If the program assumes that the first is the most important, and it assumes defaults for unspecified formats, then the cell would have blue text and a default background. OpenOffice Calc and Google Docs are examples of this.

If the program assumes that the last rule is the most important, and it assumes defaults for unspecified formats, then the cell would have a yellow background and the default color and style of text.

If the program assumes that the first is the most important, and it assumes unspecified formats are open for changes, then the cell would have blue text and a red background.

If the program assumes that the last is the most important, and it assumes unspecified formats are open for changes, then the cell would have blue text and a yellow background. Excel 2007 is an example of this.