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

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

 

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

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