Bacon Bits:

While working on a report, I decided to use some awesome custom number formatting.

This screenshot shows how I expertly apply a custom format so that any number greater than 1000 is colored green.

At this point, I’m thinking this is going to be great!

Unfortunately, this is Excel’s idea of green.

Now, we all have our opinions, but I’m telling you this is neon green. No….this is Ghostbusters green; slime green. Dare I say booger green?

To solve my green problem I decided to use a custom color tag instead of a standard VB color.

The standard VB colors are Black, White, Red, Green, Blue, Yellow, Magenta, and Cyan. These make up the first 8 of Excel’s legacy palette (the standard 56 colors that were the default in versions pre-2007).

Although you would typically specify a custom color by name, you can actually call up any one of the 56 colors defined in the standard color palette by number. You see, every color in the standard 56-color palette is represented by a number.

To call up a color by number, you would use [ColorN], where ‘N’ represents a number from 1 to 56.
 

In this example, I use [Color10] to represent green.

Here is the full syntax that tells Excel to color every number greater than 1000 green:

[Color10][>1000]#,##0.0;[Red]#,##0.0

As you can see, I get a more acceptable green.

Your next question should be, “how do you know which number represents which color”?

Well, you can run this small macro to get a list of colors and their associated numbers.

Sub ColorList()
Dim i As Integer
For i = 1 To 56
Cells(i, 1).Interior.ColorIndex = i
Cells(i, 2).Value = i
Next i
End Sub

Running the macro will give you a list similar to the list you see here (although your colors may be different based on your current color palette).

There you have it. Go and liberate yourself from booger green.

• Previous Entry: Philly CheeseBacon Sandwich
• Next Entry: Passing Multiple Values to One Parameter