Forum Discussion

SunilChitre's avatar
SunilChitre
Copper Contributor
May 30, 2022

VBA for fixing marker colors in scatter chart

I have a scatter chart where different series are configured using VBA code. I have also done MACRO to assign distinct colors for each series marker. However, when the MACRO button is clicked, on each run the marker colors update on their own, and the color scheme is disturbed.

How the coloring can be fixed, code EXCEL VBA used as below.

Sub Macro1()
'
' Macro1 Macro
'
'
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.Legend.Select
    ActiveChart.FullSeriesCollection(1).Select
    Selection.MarkerStyle = 1
    Selection.MarkerSize = 10
    With Selection.Format.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(252, 213, 181)
        .Solid
    End With
    Selection.Format.Line.Visible = msoFalse
    
    ActiveChart.FullSeriesCollection(2).Select
    Selection.MarkerStyle = 2
    Selection.MarkerSize = 10
    With Selection.Format.Fill
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorBackground2
        .ForeColor.TintAndShade = 0
        
        .Solid
    End With
    With Selection.Format.Fill
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorAccent4
        .ForeColor.TintAndShade = 0
        .Transparency = 0
        .Solid
    End With
    Selection.Format.Line.Visible = msoFalse
    
    ActiveChart.FullSeriesCollection(3).Select
    Selection.MarkerStyle = 3
    Selection.MarkerSize = 10
    With Selection.Format.Fill
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorAccent4
        .ForeColor.TintAndShade = 0
        .Solid
    End With
    With Selection.Format.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 0, 0)
        .Transparency = 0
        .Solid
    End With
    Selection.Format.Line.Visible = msoFalse
'fourth series
    ActiveChart.FullSeriesCollection(4).Select
    With Selection
        .MarkerStyle = 8
        .MarkerSize = 10
        .Format.Fill.Visible = msoTrue
        .MarkerBackgroundColor = RGB(0, 176, 80)
        .MarkerForegroundColor = RGB(0, 176, 80)
        .Format.Fill.ForeColor.TintAndShade = 0
        .Format.Fill.Solid
    End With
    With Selection.Format.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(0, 176, 80)
        .Transparency = 0
        .Solid
    End With
    Selection.Format.Line.Visible = msoFalse
    
    ActiveChart.FullSeriesCollection(5).Select
    With Selection
        .MarkerStyle = 8
        .MarkerSize = 10
    End With
    With Selection.Format.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(0, 176, 80)
        .Solid
    End With
    With Selection.Format.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(0, 176, 240)
        .Transparency = 0
        .Solid
    End With
    Selection.Format.Line.Visible = msoFalse
    
    ActiveChart.FullSeriesCollection(6).Select
    Selection.MarkerStyle = 9
    Selection.MarkerSize = 10
    With Selection.Format.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 255, 0)
        .Transparency = 0
        .Solid
    End With
    Selection.Format.Line.Visible = msoFalse
    
End Sub

Any help on errors?

No RepliesBe the first to reply

Resources