run macro on multiple charts

[Hammer_757]

I’m creating macros to help me reformat all the charts in a workboo
full of charts and I have a few questions. Ill post them separately t
keep the treads managable

1. In recording the macros to set font parameters (recorded with th
chart selected) I can use:

ActiveChart.ChartTitle.Select
Selection.AutoScaleFont = False
With Selection.Font
.Name = "Arial"
.Size = 14
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With

And just duplicate that for each font I need to set like axis title
etc. In this case it sets the active chart .

How can I code it to run all the charts on the current worksheet o
even the entire work book

 

[Debra Dalgleish]

You can loop through the charts and the worksheets:

'=====================
Sub AllChartsFormat()
Dim ws As Worksheet
Dim ch As ChartObject

On Error GoTo ExitChart
For Each ws In ActiveWorkbook.Worksheets
For Each ch In ws.ChartObjects
With ch.Chart
.PlotArea.Interior.ColorIndex = 2
.ChartArea.AutoScaleFont = False
.ChartArea.Font.Name = "Arial"
.ChartArea.Font.Size = "8"
End With
Next ch
Next ws
ExitChart:
Exit Sub
End Sub
'====================