Excel 2013 Chart Series ForeColor.RGB Ideas (VBA) - Array?

[bs0d]

I have a chart that when a new series is added, Excel automatically choses the color. I've learned it colors the series according to the Theme -->Colors --> Accents 1-6. I don't particularly like the default colors it's using in the chart for display/contrast purposes. Instead, I'd like to use VBA to pick from a pre-defined list of colors at random. For instance, an array of 10 colors and randomly select color from the array. I wanted to get some ideas on different ways to achieve this.

Note - I could just change the 6 theme accent colors to something more desirable, but it is a global change affecting other parts of the workbook, where I'd like these colors to apply to the chart only.

Here is the chart series code to apply a color using RBG

ActiveChart.FullSeriesCollection(ChartSeriesValue).Select
                With Selection.Format.Line
                    .Visible = msoTrue
                    'fixed color:
                    .ForeColor.RGB = RGB(255, 0, 0) 'comment out and Excel will cycle through 6 accent colors
                    .Transparency = 0
                    .Weight = 1
                End With

I could also randomize the color like this, but I think relying on a random color may be too risky for this project:

.ForeColor.RGB = RGB(Round((255 - 0) * Rnd + 0, 0), Round((255 - 0) * Rnd + 0, 0), Round((255 - 0) * Rnd + 0, 0))

an alternate code to change the color using msoThemeColor:

.ForeColor.ObjectThemeColor = msoThemeColorText1    'black

 

[Andrew Poulsom]

Have you tried using the ForeColor's SchemeColor property? I think the SchemeColor is ColorIndex + 7. So red would be:

ActiveChart.SeriesCollection(1).Format.Line.ForeColor.SchemeColor = 10

 

[bs0d]

Have you tried using the ForeColor's SchemeColor property? I think the SchemeColor is ColorIndex + 7. So red would be:

ActiveChart.SeriesCollection(1).Format.Line.ForeColor.SchemeColor = 10

Ok, so maybe something like this:

'Ten chart colors:
chartColors = Array(10, 12, 14, 16, 18, 20, 22, 24, 26, 28)
Num = Application.WorksheetFunction.RandBetween(LBound(chartColors), UBound(chartColors))
ActiveChart.SeriesCollection(1).Format.Line.ForeColor.SchemeColor = Num

 

[Andrew Poulsom]

That would give you a SchemeColor between 1 and 10. This seems to work:

Num = ChartColors(Application.RandBetween(LBound(ChartColors), UBound(ChartColors)))

 

[bs0d]

I see, thanks for the feedback. I think this is a good approach to get where I want.