# A Suite of XL Color Functions, Part 1

Your monitor was sold to you as being able to display more than 16 million colors. How many, exactly? 2563 or 16,777,216. One color for every combination possible of reds, greens, and blues in the range of 0 to 255 each. (Of course, unless you have 2563 pixels, you can’t see all those colors at once.) The image is of the RGB color cube, 255x255x255, with black at the origin, diagonally opposite the white corner. Excel keeps track of its colors as a long integer, the same as returned by the VBA.RGB(Red, Green, Blue) function.

RGB(1,0,0) is 1, RGB(0,1,0) is 256, and RBG(0,0,1) is 65536.
RGB(2,0,0) is 2, RGB(0,2,0) is 512, and RGB(0,0,2) is 131072.
RGB(3,3,3) is 3*1 + 3*256 + 3*65536, or 197379.

To go backwards, we can get the blue component of Excel’s colors by:
``` Function XL2BLU(xlclr As Long) As Long XL2BLU = xlclr \ 65536 End Function ```
or INT(xlclr/65536) in a spreadsheet. The green component is doing integer division on the remainder by 256.
``` Function XL2GRN(xlclr As Long) As Long XL2GRN = (xlclr – XL2BLU(xlclr) * 65536) \ 256 End Function ```
Red is what’s left after subtracting Blue and Green (a whole new meaning to subtractive colors):
``` Function XL2RED(xlclr As Long) As Long XL2RED = xlclr - XL2BLU(xlclr) * 65536 - XL2GRN(xlclr) * 256 End Function ```
To go from the XL color to RGB in one line:
``` Function XL2RGB(xlclr As Long) As Variant XL2RGB = Array(XL2RED(xlclr), XL2GRN(xlclr), XL2BLU(xlclr)) End Function ```
XL2RGB() must be array-entered across 3 cells. That version does compute XL2BLU 3 times and XL2GRN twice, but the calculations are quick ;) In even quicker form, it’s like this:
``` Function XL2RGB(xlclr as long) as Variant Dim Red as Long, Green as Long, Blue as Long```

``` Blue = xlclr\65536 Green = (xlclr - Blue*65536)\256 Red = xlclr - Blue* 65536 - Green*256 ```

``` XL2RGB = Array(Red,Green,Blue) End Function ```
The flip function, RGB2XL is this:
``` Function RGB2XL(Rng As Range) As Long Dim Red As Long, Green As Long, Blue As Long Red = Rng.Cells(1, 1) Green = Rng.Cells(1, 2) Blue = Rng.Cells(1, 3) RGB2XL = RGB(Red, Green, Blue) End Function ```
And as it must, xlclr = RGB2XL(XL2RGB(xlclr))

Up next, Part 2: XL2CMYK() and CMYK2XL() where the identity will only be “close enough.”

… mrt

## 17 thoughts on “A Suite of XL Color Functions, Part 1”

1. Rob van Gelder says:

I just added another reason to own a 4096 x 4096 monitor

2. ruve1k says:

Typo: “…and RBG(0,0,1) is 65536.”
Should be: “…and RGB(0,0,1) is 65536.”

3. How about a 256 x 256 x 256 monitor?

Thanks michael, very useful stuff.

4. Here is a single function (variations of which I have posted in the past) that will return the requested color value. The first argument is the numerical (Long) color value and the second argument is either the quoted letter “R”, “G” or “B” for the color value desired…

Function RGorB(RGBvalue As Long, R_G_B As String) As Variant
RGorB = RGBvalue \ 256 ^ (InStr(1, “RGB”, R_G_B, vbTextCompare) – 1) Mod 256
End Function

In addition, here are some alternates for the blog article’s XL2BLU, XL2GRN and XL2RED functions…

Function XL2RED(xlclr As Long) As Long
XL2RED = CLng(“&H” & Right(Hex(xlclr), 2))
End Function

Function XL2GRN(xlclr As Long) As Long
XL2GRN = CLng(“&H” & Mid(Hex(xlclr), 3, 2))
End Function

Function XL2BLU(xlclr As Long) As Long
XL2BLU = CLng(“&H” & Left(Hex(xlclr), 2))
End Function

5. MSimms says:

What about a color picker in Excel ? There’s one for Access. I need users to select a set of font and interior cell colors for their application.
Providing them 3 DROP downs of 0 to 255, just doesn’t “hack” it.

6. Michael says:

Rick –

XL2HEX() and HEX2XL() are planned as Part 3 ;) XL has red as least significant and Hex has Red as most significant. Once the hex number is front padded to six characters, I use your functions exactly. Well, almost exactly…I lower cased the “H” ;-)

M-

My topic after color functions is to show how to get a list of installed fonts into a form’s combo box. Will that help? J-Walk’s site has an old-fashioned color picker that gives you your choice of 56. Still works–I used it last week. It’s on my list to figure out how to access the new color picker. J-Walk also has the method I modify to get the font list. Search his site. Once John shows the way, this is straight forward stuff. ;-0

… mrt

7. Tushar Mehta says:

Color is an integer between 0 and 255 * 255 * 255. There are perfectly good arithmetic operators to get the result in a straightforward manner. {grin}

Other points to consider. R, G, and B are integer values between 0 and 255. The appropriate data type is Byte.

Also, I stay away from variables named xl… since Microsoft uses xl… for Excel related constants and mnemonics.

So,

`Option Explicit`

``` Function RGBComponents(ByVal aColor As Long) Dim R As Byte, G As Byte, B As Byte R = aColor Mod 256 G = (aColor \ 256) Mod 256 B = aColor \ (256& * 256) RGBComponents = Array(R, G, B) End Function Function VBARGB(ByVal R As Byte, ByVal G As Byte, _ ByVal B As Byte) As Long VBARGB = RGB(R, G, B) End Function The name of the last function is a convention I've used for several years. When I create a UDF to export a VBA function for use in Excel, I use the name VBA{VBA function anme} ```

```An alternative that also provides individual colors... `````` Option Explicit```

``` Function RGB_R(ByVal aColor As Long) As Byte RGB_R = aColor Mod 256 End Function Function RGB_G(ByVal aColor As Long) As Byte RGB_G = (aColor \ 256) Mod 256 End Function Function RGB_B(ByVal aColor As Long) As Byte RGB_B = aColor \ (256& * 256) End Function Function RGBComponents(ByVal aColor As Long) RGBComponents = Array(RGB_R(aColor), RGB_G(aColor), RGB_B(aColor)) End Function Function VBARGB(ByVal R As Byte, ByVal G As Byte, _ ByVal B As Byte) As Long VBARGB = RGB(R, G, B) End Function ```

```To get the fill color of a cell, keeping in mind that it will not automatically recompute when the color changes. `````` Function CellColor(aCell As Range) CellColor = RGBComponents(aCell.Interior.Color) End Function```

``` ```

`Finally, if there is a compelling need to get individual colors through a single function with the desired color passed in as a 1 character string, I would use a Select Case statement. This will also allow for error handling.`

8. Michael says:

Hi Tushar –

Thank you for the powerful and insightful functions. Using MOD gives those one-liners I appreciate. I use “xlclr” actually for just the reasons you advise against: I know where it’s coming from. I’m stealing your naming tip.

Our method of finding blue is the same, I think. Considering that it might just be ctrl-V run amok (you used it twice), is there an “&*” operation? Talking to my wife, I’d say no. Talking to you, I’d not be surprised.

… mrt

9. SVBA says:

I’m sorry Rick Rothstein but it’s impossible to use LEFT RIGHT MID functions.

With only red, you have only 2 chars…
Mid(Hex(xlclr), 3, 2)) failed

SVBA

10. Michael says:

SVBA –

No, Red is FF0000. Note that you can’t take the xlclr to HEX directly. The Reds are at opposite ends.

… mrt

11. Tushar Mehta says:

Hi Michael,

True, the function argument can come from an Excel cell, but it is not limited to use in an Excel cell. {grin} When I first had to work with colors, the need was for functions called from other functions/subs. The fact that they met UDF requirements was intentional but not the primary reason for the development of the functions.

256& causes the VBA compiler to treat 256 as a long rather than an integer. Otherwise (256 * 256) will cause an overflow error! I could have been more explicit and used CLng(256) * 256, I suppose, or I could have used aColor \ 256 \ 256.

12. Michael says:

Hi Tushar –

Thanks. I get it now. I knew there was a reason I declared the colors as Long. It’s just not the one I thought it was ;-)

… mrt

13. SVBA says:

Hi Michael,

i would say, you can’t use like that:
Image1.BackColor = VBARGB(XL2RED(ActiveCell.Interior.Color), _
XL2GRN(ActiveCell.Interior.Color), _
XL2BLU(ActiveCell.Interior.Color))

Because with black -> &h0 -> 1char
red -> &HFF -> 2 chars

14. Michael says:

Hi SVBA –

Please see Part 3, just posted. You “know” the ActiveCell.Interior.Color. It’s a XL long integer. If you use my functions, why do you want to break it down into its red, green, and blue parts, (all decimal values) feed it to RGB() and get it back as the XL long integer you started with? If you use Rick’s functions, Rick assumed you padded the hexclr with leading zeros to get it six characters long. Black is &h000000. Still, for your example, you’re taking something to hex, breaking it down, and putting it back together. All you need to say is Image1.BackColor = ActiveCell.Interior.Color

Red, RGB(255,0,0) = 255, and 255 is FF (base 16), that true, but that’s not the hexadecimal color red. That’s XL’s red expressed in hex. These are two different numbering schemes. If you want to take red to the web, it’s FF0000. If you just take FF to the web, you get 0000FF and that’s blue.

If Part 3 doesn’t clear this up, let’s continue the conversation over there.

… mrt

15. MSimms says:

This is great, but what about a color picker for Excel ?
3 RGB combo boxes just doesn’t “hack it” !

Posting code? Use <pre> tags for VBA and <code> tags for inline.