Your monitor was sold to you as being able to display more than 16 million colors. How many, exactly? 256^{3} 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 256^{3} 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**©¿©¬*

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

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

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

How about a 256 x 256 x 256 monitor?

Thanks michael, very useful stuff.

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

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.

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

… mrtColor 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.`

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.

… mrtI’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

SVBA –

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

… mrtHi 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.

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 ;-)

… mrtHi 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

…

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.

… mrtThis is great, but what about a color picker for Excel ?

3 RGB combo boxes just doesn’t “hack it” !

M-

This is the color picker I mentioned above.

http://www.j-walk.com/ss/excel/tips/tip49.htm

… mrtM –

And here is how to access a modern one:

http://vba-corner.livejournal.com/1691.html

… mrt