How to get a scatter Chart Series MarkerBackgroundColor if marker filling is set to Automatic?

Question

I have a scatter chart with multiple series and a trendline per series. I want to set the trendline datalable to be the same color as the series' fill color. The series' fill color is Automatic. The .MarkerBackgroundColor property returns -1 for all series while the .MarkerBackgroundColorIndex returns 2 for all series. I believe this only happens if the marker fill is set to Automatic. However, I can't change that because the series in the chart get added through another VBA code based on dynamic data. Any help?

For i = 1 To Sheet1.ChartObjects("Chart 5").Chart.SeriesCollection.Count
    With Sheet1.ChartObjects("Chart 5").Chart.FullSeriesCollection(i)
       .Trendlines(1).DataLabel.Format.Fill.ForeColor.RGB = .MarkerBackgroundColor
    End With
Next i

Answer 1

In an XY Scatter chart with the marker fill set to "automatic", Excel will apply the colors in the same order as the six theme accent colors in the current palette.

If the chart has more than six series, a lighter/darker shade of these accent colors will be applied, again in the same order. How much lighter/darker actually varies with the Excel version. The screenshot shows the legend for the first 12 series and the default "Office" palette for Office 365 for your reference.

So, if you know the order in which the series sits in the chart, you can surmise its fill color.

Answer 2

Thanks teylyn.

Actually, I made a quick workaround by utilizing the .Trendlines(1).Format.Line.ForeColor.RGB instead of the .MarkerBackgroundColor and it surprisingly works although the Trendline color is set to Automatic as well!

Here is the edited code:

For i = 1 To Sheet1.ChartObjects("Chart 5").Chart.SeriesCollection.Count
    With Sheet1.ChartObjects("Chart 5").Chart.FullSeriesCollection(i)
       .Trendlines(1).DataLabel.Format.Fill.ForeColor.RGB = .Trendlines(1).Format.Line.ForeColor.RGB
    End With
Next i