Format multiple series on a chart with macro

[ttbuson]

I have the following vba code obtained from the macro recorder for changing a data series in an xy scatterplot to a black diamond. Is there any way to edit this so that it does it for all series on a chart? I have about 50 series and would like the macro to go through each one and do the same thing. It would also be helpful to do it for certain series. Thanks!

Sub Format_RR_dataseries()

    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(11).Select
    With Selection
        .MarkerStyle = 2
        .MarkerSize = 7
        .Format.Fill.ForeColor.ObjectThemeColor = msoThemeColorText1
        .Format.Line.Visible = msoFalse
    End With
    Range("I14").Select
End Sub

 

[pgc01]

Hi

Try:

Sub Format_RR_dataseries()
Dim ser As Series
 
    With ActiveSheet.ChartObjects("Chart 1").Chart
        For Each ser In .SeriesCollection
            With ser
                .MarkerStyle = 2
                .MarkerSize = 7
                .Format.Fill.ForeColor.ObjectThemeColor = msoThemeColorText1
                .Format.Line.Visible = msoFalse
            End With
        Next ser
    End With
End Sub

 

[ttbuson]

Awesome, this works great! How would I edit it to only apply the macro to a certain series range like say 1 to 12 or 20 to 30?

 

[pgc01]

Hi again

Try:

Sub Format_RR_dataseries()
Dim j As Long
 
    With ActiveSheet.ChartObjects("Chart 1").Chart
        For j = 1 To 12
            With .SeriesCollection(j)
                .MarkerStyle = 2
                .MarkerSize = 7
                .Format.Fill.ForeColor.ObjectThemeColor = msoThemeColorText1
                .Format.Line.Visible = msoFalse
            End With
        Next j
    End With
End Sub

 

[ttbuson]

That worked great, thanks!

 

[pgc01]

You are welcome. Thanks for the feedback.