excel - 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

标签： excelvbacharts

Answer 1

在标记填充设置为“自动”的 XY 散点图中，Excel 将按照与当前调色板中的六种主题强调色相同的顺序应用颜色。

如果图表有超过六个系列，则将应用这些强调色的较浅/较深的阴影，再次以相同的顺序。实际上，更亮/更暗的程度因 Excel 版本而异。屏幕截图显示了前 12 个系列的图例和 Office 365 的默认“Office”调色板，供您参考。

因此，如果您知道该系列在图表中的排列顺序，您就可以推测其填充颜色。