Apply Macro to ALL Charts

[JonesyUK]

Hi Please Help:

I've recorded the following Macro in Excel:

ActiveSheet.ChartObjects("Chart 8").Activate
ActiveChart.SeriesCollection(1).Select
ActiveChart.ChartGroups(1).GapWidth = 0
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorAccent1
.ForeColor.TintAndShade = 0.3399999738
.ForeColor.Brightness = 0
.BackColor.ObjectThemeColor = msoThemeColorAccent1
.BackColor.TintAndShade = 0.7649999857
.BackColor.Brightness = 0
.TwoColorGradient msoGradientHorizontal, 1
End With
Selection.Format.Shadow.Type = msoShadow24

End Sub

I need to run this macro on about 200 charts which are all on a sheet named "CHARTS"

Can you please help me to edit this macro so that it goes through all the charts and applies these formats?

Thanks!
C

 

[pgc01]

Hi

Try this code. Amend the code for the name of the worksheet.

Sub Test()
Dim ws As Worksheet
Dim chtO As ChartObject
 
Set ws = Worksheets("Sheet1")
 
For Each chtO In ws.ChartObjects
    With chtO.Chart
        .ChartGroups(1).GapWidth = 0
        With .SeriesCollection(1)
            With .Format.Fill
                .Visible = msoTrue
                .ForeColor.ObjectThemeColor = msoThemeColorAccent1
                .ForeColor.TintAndShade = 0.3399999738
                .ForeColor.Brightness = 0
                .BackColor.ObjectThemeColor = msoThemeColorAccent1
                .BackColor.TintAndShade = 0.7649999857
                .BackColor.Brightness = 0
                .TwoColorGradient msoGradientHorizontal, 1
            End With
            .Format.Shadow.Type = msoShadow24
        End With
    End With
Next chtO
End Sub

 

[JonesyUK]

Thanks ever so much, this is perfect....

It's so frustrating to need to make a small change to the charts, because it's easy for 1, but not so easy for 200!! And most of the time I can't find the applicable objects in the Object Library....

Thanks Again...

 

[pgc01]

Thanks for the feedback. I'm glad it helped.