Excel Conditional Formatting: Considering the '4th' option

Back

About MrExcel

Consulting Services

Learn Excel Resources

Challenge of the Month

Past Tip of the Day

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.

IML

By Ian Loughlin on 10-May-2002

MrExcel.com Consulting can be hired to implement this concept, or many other cool applications, with your data.

MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures on this web site are provided "as is" and we do not guarantee that they can be used in all situations.

Excel is a registered trademark of the Microsoft® Corporation.