Excel根据单元格颜色设置折线图颜色

https://www.coder.work/article/7850118

 

• 遍历 SeriesCollection的Chart并捕获Formula每个 Series .
• 使用 Split 获取对源数据(公式的第 3 部分)的引用.
• 设置ForeColor.RGB每个 Series等于 Interior.Color与其关联的数据范围。

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

Sub ColorMyChart()     Dim myChart As ChartObject     Set myChart = Sheet1.ChartObjects("Chart 1")       With myChart.Chart         Dim i As Integer           For i = 1 To .SeriesCollection.Count               Dim dataAddress As String             ' takes a formula like =SERIES(Sheet1!$A$2,Sheet1!$B$1:$K$1,Sheet1!$B$2:$K$2,1)             ' and returns Sheet1!$B$2:$K$2             dataAddress = Split(.SeriesCollection(i).Formula, ",")(2)               Dim dataColor As Long             ' assumes the data range all has the same color             dataColor = Sheet1.Range(dataAddress).Interior.Color               .SeriesCollection(i).Format.Line.ForeColor.RGB = dataColor         Next i     End With End Sub