cellMatrix.net

Get RGB From Embedded Chart Series

Earlier this week I received a workbook from a coworker containing several embedded column charts. The coworker needed to replicate the colors for each bar in each chart. Unfortunately the color shades for each bar were so close to each other we couldn't tell where the colors were represented in the workbook's palette. After some modification of code found at Dave McRitchie's site, we came up with the following to get the RGB for each bar:

Sub Get_Series_RGB()

    Dim Cht As Chart
    Dim Srs As Series
    Dim R As Integer
    Dim G As Integer
    Dim B As Integer
    Dim Rng As Range

    Set Cht = ActiveChart

    Worksheets.Add().Name = "RGB"
    Set Rng = Sheets("RGB").Range("A1")

    Rng.Value = "Series"
    Rng.Offset(0, 1).Value = "Red"
    Rng.Offset(0, 2).Value = "Green"
    Rng.Offset(0, 3).Value = "Blue"

    Set Rng = Rng.Offset(1, 0)

    For Each Srs In Cht.SeriesCollection

        GetRGB Srs.Interior.Color, R, G, B

        Rng.Value = Srs.Name
        Rng.Interior.Color = Srs.Interior.Color
        Rng.Offset(0, 1).Value = R
        Rng.Offset(0, 2).Value = G
        Rng.Offset(0, 3).Value = B

        Set Rng = Rng.Offset(1, 0)

    Next Srs

End Sub

Sub GetRGB(RGB As Long, ByRef Red As Integer, _
           ByRef Green As Integer, ByRef Blue As Integer)
    Red = RGB And 255
    Green = RGB \ 256 And 255
    Blue = RGB \ 256 ^ 2 And 255
End Sub

For example, based on the following chart:

The macro produces the following RGB values for each series:

Finally, it should be noted that RGB returned above may not always be the actual RGB of the color shown.