Return RGB values from Range.Interior.Color (or any other Color Property)

Question

I was trying to incrementally change the background color of a cell to black, and I found that the Range.Interior.Color method returns a Long which is seemingly arbitrary. Looking at the documentation on MSDN, there is nearly nothing about what this number represents. Is there a way to return the RGB value from this long. I effectively need the opposite of the RGB(red, green, blue) function.

Answer 1

That "arbitrary" number is a mathematical combination of the RGB values (B256^2 + G256 + R) and a conversion of the hex color value to a decimal number (base 16 to base 10), depending on which way you want to look at it. Just different bases. Below is the method I use in the XLAM addin file I wrote for Excel. This method has come in handy many times. I have included the documentation in my addin file.

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' '   Function            Color '   Purpose             Determine the Background Color Of a Cell '   @Param rng          Range to Determine Background Color of '   @Param formatType   Default Value = 0 '                       0   Integer '                       1   Hex '                       2   RGB '                       3   Excel Color Index '   Usage               Color(A1)      -->   9507341 '                       Color(A1, 0)   -->   9507341 '                       Color(A1, 1)   -->   91120D '                       Color(A1, 2)   -->   13, 18, 145 '                       Color(A1, 3)   -->   6 '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Function Color(rng As Range, Optional formatType As Integer = 0)     As Variant     Dim colorVal As Variant     colorVal = rng.Cells(1, 1).Interior.Color     Select Case formatType         Case 1             Color = WorksheetFunction.Dec2Hex(colorVal, 6)         Case 2             Color = (colorVal Mod 256) & ", " & ((colorVal \ 256) Mod 256) & ", " & (colorVal \ 65536)         Case 3             Color = rng.Cells(1, 1).Interior.ColorIndex         Case Else             Color = colorVal     End Select End Function 

Answer 2

good to see that Mr Wyatt uses the fast method of color to RGB

R = C Mod 256 G = C \ 256 Mod 256 B = C \ 65536 Mod 256 

which is many times faster than those using hex str with left mid right that some recommend