General XL Tips

XL Formulae

Using VBA with XL

XL VBA Functions

XL VBA Macros

Mac XL

This site will look better if you upgrade to a browser that supports web standards.

Getting 6 conditional font colors without macros

XL's Conditional Formatting (Format/Conditional Formatting...) allows 3 conditional formats to be applied. Users often ask for ways to extend that to 4 or more formats. If you're changing background color, borders, bolding, etc., you're best off using the Worksheet_Change() or Worksheet_Calculate() event macros to accomplish this.

However, if you're simply looking to change font color, you can use XL's custom formatting codes to provide up to 3 more options.

Formatting codes consist of 4 fields separated by semicolons. By default, the first field is the format applied to positive numbers, the second to negative numbers, the third to zero and the fourth to text. You can adjust that with conditional statements, however. Say we wanted to format according to the following scheme:

        Value        Font Color
        =====        ==========
       < -100        Red
       <  -20        Blue
       <    0        Yellow
       <   20        Green
       <  100        Maroon
       >= 100        Purple

The last three we'll leave for conditional formatting. The first three, however, will be done using this format, which we enter by choosing Format/Cells/Number/Custom:


Of course, we don't have to use General - we could use any other numeric format. The available color names are [BLACK], [BLUE], [CYAN], [GREEN], [MAGENTA], [RED], [WHITE], and [YELLOW], or you can use any of the 56 colors in the XL color palette by using [Color1] through [Color56]. Thanks, MVP Bob Umlas for telling me about the latter method!

For more, see XL's Help ("About custom number formats").

Valid XHTML 1.1 Valid CSS Made on a Macintosh

Quick Links

David McRitchie's Conditional Format page

Chip Pearson's Conditional Format page

Chip's Color Banding using CF page

Using CF to highlight cells with formulas (John Walkenbach)