This site will look better if you upgrade to a browser that supports web standards.
This site will look better if you upgrade to a browser that supports web standards.
McGimpsey & Associates This site will look better if you upgrade to a browser that supports web standards.
XL's 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 :
[Red][<-100]General;[Blue][<-20]General;[Yellow]General;@
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").
This page last updated
© Copyright 2001 - 2004 McGimpsey and Associates. Except where noted, all code on this site may be distributed under the Gnu GPL. Acknowledgement is appreciated.
