May 30 2022 08:25 AM
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?