VBA Change Line Color of Chart with ColorIndex

Question

The code below works and sets the color of the four lines with the three values for rgb. The three rgb values are filled in an array (Clr) which is not very clear. Is there an alternative way for instant by using Hex numbers, ColorIndex or the predefined colors like vbBlack?

Sub CreateChart()
Dim i As Long
Dim j As Long
Dim Clr() As Variant

'Clr = Array("vbBlack", "vbBlue", "vbRed", "vbCyan", "vbGreen", "vbMagenta")    ' colors as types
'Clr = Array("&H0", "&HFF0000", "&Hff", "&HFFFF00", "&HFF00", "&HFF00FF")       ' Hex of colors as String
'Clr = Array(&H0, &HFF0000, &HFF, &HFFFF00, &HFF00, &HFF00FF)                   ' Hex Values of colors
'Clr = Array(&H0, &HFF0000, &HFF, &HFFFF00, &HFF00, &HFF00FF)                   ' IndexColor
Clr = Array(0, 0, 0, 0, 0, 255, 255, 0, 0, 0, 255, 255, 0, 255, 0, 255, 0, 255)
With Charts.Add
    .ChartType = xlXYScatterLinesNoMarkers
    .SetSourceData Source:=Sheets("Plot").Range("A12:E213")
    j = 0
    For i = 2 To 5
        With .FullSeriesCollection(i - 1).Format.Line
            .ForeColor.RGB = RGB(Clr(j), Clr(j + 1), Clr(j + 2))
            j = j + 3
        End With
    Next
End With
   
End Sub

ColorIndex is from an old color system in Excel 97-2003. It's still available, but is mostly not very useful, since now colors can be specified directly using RGB. — Jon Peltier, May 09 '22 at 00:10

score 1 · Answer 1 · answered May 07 '22 at 14:47

Use the Hex2Dec() WorkSheetFunction ?

Sub CreateChart()
    Dim i As Long
    Dim j As Long
    Dim Clr() As Variant

    'Clr = Array("vbBlack", "vbBlue", "vbRed", "vbCyan", "vbGreen", "vbMagenta")    ' colors as types
    'Clr = Array("&H0", "&HFF0000", "&Hff", "&HFFFF00", "&HFF00", "&HFF00FF")       ' Hex of colors as String
    Clr = Array(&H0, &HFF0000, &HFF, &HFFFF00, &HFF00, &HFF00FF)                   ' Hex Values of colors
    'Clr = Array(&H0, &HFF0000, &HFF, &HFFFF00, &HFF00, &HFF00FF)                   ' IndexColor
    'Clr = Array(0, 0, 0, 0, 0, 255, 255, 0, 0, 0, 255, 255, 0, 255, 0, 255, 0, 255)
    With Charts.Add
        .ChartType = xlXYScatterLinesNoMarkers
        .SetSourceData Source:=Sheets("Plot").Range("A12:E213")
        j = 0
        For i = 2 To 5
            With .FullSeriesCollection(i - 1).Format.Line
                .ForeColor.RGB = WorksheetFunction.Hex2Dec(Clr(j))
                j = j + 1
            End With
        Next i
    End With
End Sub

score 1 · Accepted Answer · answered May 07 '22 at 15:09

Actually, your code works well, just get rid of quotation marks and RGB function. Also there's nothing wrong with hex values, they work fine.

Sub CreateChart()
Dim Color As Variant
Dim Source as Range
Dim i As Long

    Set Source = Sheets("Plot").Range("A12:E213")
    Color = Array(vbBlack, vbBlue, vbRed, vbCyan, vbGreen, vbMagenta)
    ' Array(&H0, &HFF0000, &HFF, &HFFFF00, &HFF00, &HFF00FF) works as well
    With Charts.Add
        .ChartType = xlXYScatterLinesNoMarkers
        .SetSourceData Source
        For i = 1 To .FullSeriesCollection.Count
            .FullSeriesCollection(i).Format.Line.ForeColor.RGB = Color(i - 1)
        Next i
    End With

End Sub

To use Color(i) instead of somewhat clumsy Color(i - 1) we should add Option Base 1 to start Array index from one by default. But it's up to you, of course.

P.S. Take a look at xlRGBColor enumeration

Thank you, it works! I didn't know about the xlRGBColor enumeration! This is really a great color collection. — musbach, May 07 '22 at 20:08

VBA Change Line Color of Chart with ColorIndex

2 Answers2