VBA Change Line Color of Chart with ColorIndex-CodePudding

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

CodePudding user response：

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

CodePudding user response：

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