Return RGB values from Range.Interior.Color (or an

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

Short Answer:

There is no built in functionality for this. You must write your own function.

Long Answer:

The long that is returned from the Interior.Color property is a decimal conversion of the typical hexidecimal numbers that we are used to seeing for colors in html e.g. "66FF66". Additionally the constant xlNone (-4142) can be passed to set cell to have no color in the background, however such cells are marked white RGB(255, 255, 255) from the Get property. Knowing this, we can write a function that returns one or all of the appropriate RGB values.

Luckily, a kind Mr. Allan Wyatt has done just that here!

Determining the RGB Value of a Color

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

Answer 3

Should note, for the hex values, if you're exporting out to HTML you're going to get quirks too.

Ideally you'd create the hex string from the individual colours, rather than returning a hex from the ColorVal number.

The reason being you can get some invalid hex numbers if the cell is a 'pure' colour like green/blue

RED - RGB(255,0,0) returns 'FF' - it should return 'FF0000'

BLUE - RGB(0,0,255) returns 'FF00000' - it should return '0000FF'

enter image description here

If you used these to create HTML/CSS colour output, you'd get RED for any blue cells.

I modified the script to assemble each two character hex 'chunk' based on the RGB values, with a UDF that just pads with a leading 0 where output of one character is returned ( hopefully if you're reading this, you can make something similar )

Color = ZeroPad(Hex((colorVal Mod 256)), 2) & ZeroPad(Hex(((colorVal \ 256) Mod 256)), 2) & ZeroPad(Hex((colorVal \ 65536)), 2)

--Edit : forgot to include the code for the UDF...

Function ZeroPad(text As String, Cnt As Integer) As String
'Text is the string to pad
'Cnt is the length to pad to, for example  ZeroPad(12,3) would return a string '012' , Zeropad(12,8) would return '00000012' etc..
Dim StrLen As Integer, StrtString As String, Padded As String, LP As Integer


StrLen = Len(Trim(text))


    If StrLen < Cnt Then

        For LP = 1 To Cnt - StrLen

            Padded = Padded & "0"

        Next LP

    End If

ZeroPad = Padded & Trim(text)

ENDOF:


End Function

Answer 4

Mark Balhoff´s VBA script works fine. All credits go to him.

In case you´d like to get the color codes/indexes of conditionally formatted cells as well, the code may be amended like this:

'----------------------------------------------------------------
'   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             color of cell, not considering conditional formatting color
'                       1   Hex                 color of cell, not considering conditional formatting color
'                       2   RGB                 color of cell, not considering conditional formatting color
'                       3   Excel Color Index   color of cell, not considering conditional formatting color
'                       4   Integer             "real" visible color of cell (as the case may be the conditional formatting color)
'                       5   Hex                 "real" visible color of cell (as the case may be the conditional formatting color)
'                       6   RGB                 "real" visible color of cell (as the case may be the conditional formatting color)
'                       7   Excel Color Index   "real" visible color of cell (as the case may be the conditional formatting color)
'   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
    Select Case formatType
        Case 0 To 3
            colorVal = Cells(rng.Row, rng.Column).Interior.Color
        Case 4 To 7
            colorVal = Cells(rng.Row, rng.Column).DisplayFormat.Interior.Color
    End Select
    Select Case formatType
        Case 0
            Color = colorVal
        Case 1
            Color = Hex(colorVal)
        Case 2
            Color = (colorVal Mod 256) & ", " & ((colorVal \ 256) Mod 256) & ", " & (colorVal \ 65536)
        Case 3
            Color = Cells(rng.Row, rng.Column).Interior.ColorIndex
        Case 4
            Color = colorVal
        Case 5
            Color = Hex(colorVal)
        Case 6
            Color = (colorVal Mod 256) & ", " & ((colorVal \ 256) Mod 256) & ", " & (colorVal \ 65536)
        Case 7
            Color = Cells(rng.Row, rng.Column).DisplayFormat.Interior.ColorIndex
    End Select
End Function

Answer 5

That "arbitrary" number is a mathematical combination of the RGB values (B*256^2 + G*256 + 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 = Cells(rng.Row, rng.Column).Interior.Color
    Select Case formatType
        Case 1
            Color = Hex(colorVal)
        Case 2
            Color = (colorVal Mod 256) & ", " & ((colorVal \ 256) Mod 256) & ", " & (colorVal \ 65536)
        Case 3
            Color = Cells(rng.Row, rng.Column).Interior.ColorIndex
        Case Else
            Color = colorVal
    End Select
End Function

Answer 6

The other answer did not work for me. I found that:

R = C And 255
G = C \ 256 And 255
B = C \ 256 ^ 2 And 255

and it worked properly.