Conditional Formatting Considering the 4th Option

May 10, 2002 - by Ian Loughlin

Conditional formatting allows for 3 conditions. By setting up conditions correctly (least to most restrictive), you can really allow for 4 conditions when the default formatting of the cell is considered. For example, if you want to fill cells less than or equal to -25 red, between 0 and -25 yellow, 0 to 25 green, and over 25 black, this can be achieved by doing the following:

Select your range of cells.
Select conditional formatting from the Format menu.
From the drop down menu, set condition 1 to “Cell Value Is” and “Less than or Equal to” and put in the value -25. Now press the format button and choose red from the patterns tab and click Okay.
Click add, and repeat the above steps changing the value to 0 and a color to yellow for your second condition. Click add again, and repeat with a value of 25 and a green color. Click okay to exit the conditional formatting dialog box.
Finally, while your range is still highlighted, choose a black fill from the color palette, and your range is now pseudo-conditionally formatted to cover your four cases.