About MrExcel
Consulting Services
Learn Excel Resources
Challenge of the Month
MrExcel Seminars

Message Board

MrExcel Store


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.


By Ian Loughlin on 10-May-2002 Consulting can be hired to implement this concept, or many other cool applications, with your data. 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.

All contents Copyright 1998-2008 by MrExcel Consulting.