Search code examples
excelvbacolorsexcel-charts

VBA Scatter Chart Line Color Access

Is there a way to access the color of a new series line in a scatter chart that is created dynamically using VBA? I want to take the color of the line of the new series and put it as the background of a cell near where the data is for the new series.

I can't seem to find how to access the color of a series when it is created.

Solution

  • If I record a macro while setting the color I get:

    Sub Macro1()
    ActiveSheet.ChartObjects("Chart 2").Activate
    ActiveChart.FullSeriesCollection(1).Select
    With Selection.Format.Line
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 0, 0)
        .Transparency = 0
    End With
    Range("I29").Select
End Sub

    So to read the value you can do something like:

    Sub Tester()
    Dim cht As Chart
    Set cht = ActiveSheet.ChartObjects(1).Chart
    Debug.Print cht.SeriesCollection(1).Format.Line.ForeColor.RGB
End Sub