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