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
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
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
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))
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)
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)
And as it must, xlclr = RGB2XL(XL2RGB(xlclr))
Up next, Part 2: XL2CMYK() and CMYK2XL() where the identity will only be “close enough.”