VBA to return RGB Color Code

[chingching831]

Hi there,

May I know if there's a VBA Code that can return the RGB colour code of a coloured cell?

Thanks in advance!

Best,
SC

 

[dmt32]

May I know if there's a VBA Code that can return the RGB colour code of a coloured cell?

Have a look here: Determining the RGB Value of a Color

Dave

 

[jolivanes]

Colors starting at A2 on down.

Sub Maybe()
Dim i As Long, N As Long, R As Long, G As Long, B As Long
For i = 2 To 6    '<---- 31 in your example
    With Cells(i, 1)
        N = .Interior.Color
        B = Int(N / 65536)
        G = Int(((N / 65536) - B) * 256)
        R = N - (B * 65536) - (G * 256)
    .Offset(, 1).Value = R
    .Offset(, 2).Value = G
    .Offset(, 3).Value = B
    .Offset(, 4).Value = N
    .Offset(, 5).Value = "RGB(" & R & ", " & G & ", " & B & ")"
    End With
Next i
End Sub

 

[chingching831]

Works well! Thanks a lot!

Colors starting at A2 on down.

Sub Maybe()
Dim i As Long, N As Long, R As Long, G As Long, B As Long
For i = 2 To 6    '<---- 31 in your example
    With Cells(i, 1)
        N = .Interior.Color
        B = Int(N / 65536)
        G = Int(((N / 65536) - B) * 256)
        R = N - (B * 65536) - (G * 256)
    .Offset(, 1).Value = R
    .Offset(, 2).Value = G
    .Offset(, 3).Value = B
    .Offset(, 4).Value = N
    .Offset(, 5).Value = "RGB(" & R & ", " & G & ", " & B & ")"
    End With
Next i
End Sub

 

[chingching831]

Colors starting at A2 on down.

Sub Maybe()
Dim i As Long, N As Long, R As Long, G As Long, B As Long
For i = 2 To 6    '<---- 31 in your example
    With Cells(i, 1)
        N = .Interior.Color
        B = Int(N / 65536)
        G = Int(((N / 65536) - B) * 256)
        R = N - (B * 65536) - (G * 256)
    .Offset(, 1).Value = R
    .Offset(, 2).Value = G
    .Offset(, 3).Value = B
    .Offset(, 4).Value = N
    .Offset(, 5).Value = "RGB(" & R & ", " & G & ", " & B & ")"
    End With
Next i
End Sub

Have a look here: Determining the RGB Value of a Color

Dave

Thank you!

 

[Jaafar Tribak]

Yet another neat way that makes use of the little known vba LSET statement:

Option Explicit

Private Type UDT_RGB
    r As Byte
    g As Byte
    b As Byte
End Type

Private Type UDT_LONG
    n As Long
End Type

Function LongToRGB(ByVal Col As Long) As UDT_RGB
    Dim uRGB As UDT_RGB, uDummy As UDT_LONG
    uDummy.n = Col
    LSet uRGB = uDummy
    With LongToRGB
        .r = uRGB.r: .g = uRGB.g:  .b = uRGB.b
    End With
End Function

Usage Example:

Sub Test()
    'Copy Interior color from Cell 'A1' to Cell 'C1'
    Dim lCol As Long, uRGB As UDT_RGB
    lCol = Range("A1").Interior.Color
    uRGB = LongToRGB(lCol)
    With uRGB
        Debug.Print "Red: "; "&H"; Hex(.r), "Green: "; "&H"; Hex(.g), "Blue: "; "&H"; Hex(.b)
        Range("C1").Interior.Color = rgb(.r, .g, .b)
    End With
End Sub