Chart formatting loop (Font Name) VBA

[ckvaternik]

Hey everyone,

I am trying to find resources on here (unsuccessfully) to write a VBA loop that will go through each chart on each sheet of a workbook and change the font of all labels, series, chart titles etc.

I am having trouble understanding how to write such a loop. I have tried using the recorder to help me get a basic syntax, but so far, I can't get it.

Does anyone have any ideas?

Thanks!

 

[AlphaFrog]

Sub Chanage_Chart_Fonts()

    Dim ws As Worksheet
    Dim ch As ChartObject
    Dim ser As Series
    
    For Each ws In ActiveWorkbook.Worksheets
        If ws.ChartObjects.Count > 0 Then
            For Each ch In ws.ChartObjects
            
                With ch.Chart
                
                    ' Title
                    With .ChartTitle.Font
                        .Name = "Arial"
                        .FontStyle = "Bold"
                        .Size = 11
                    End With
                    
                    ' Legend
                    With .Legend.Font
                        .Name = "Arial"
                        .FontStyle = "Regular"
                        .Size = 9
                    End With
                    
                    ' DataLabels
                    For Each ser In .SeriesCollection
                        If ser.HasDataLabels Then
                            With ser.DataLabels.Font
                                .Name = "Arial"
                                .FontStyle = "Regular"
                                .Size = 8
                            End With
                        End If
                    Next ser
                    
                End With
            Next ch
        End If
    Next ws
    
End Sub