How to format font in all charts in chart group

Question

I'm trying to make sure all of the fonts in charts on a worksheet the same and that all title fonts are the same. I have different types of charts so I have the charts in chart groups.

I read that you have to active each chart in order to format it. I think I have to use ChartObject.

Sub Macro2()

Dim cover As Worksheet
Dim groupIndex As Long
Dim seriesIndex As Long
Dim cht As ChartObject
Set cover = Sheets("Sheet1")

cover.Activate

For Each cht In cover.Shapes.Range(Array("Group1")).GroupItems
cht.Activate
    ChartArea.ChartTitle.Font.Size = 12
          With ActiveChart.ChartArea.Font
              BaselineOffset = 0
              Bold = msoFalse
              FontColor = vbRed
              FontSize = 10
          End With
Next
End Sub

I'm getting a type mismatch for the For statement.

I'm not sure about if they have to be active to access the .Font properties of all the elements in a chart object. I don't think it has to be active. Either way, within your With statement you forgot to put a . in front of the font properties. — JvdV

Tim Williams Tim Williams · Accepted Answer · 2019-07-29T20:15:52

The Chart property of ChartObject is what you need.

Try this:

Sub Macro2()

    Dim cover As Worksheet
    Dim groupIndex As Long
    Dim seriesIndex As Long
    Dim cht '<< as variant
    Set cover = Sheets("Sheet1")

    For Each cht In cover.Shapes.Range(Array("Group1")).GroupItems
        With cht.Chart
            .ChartTitle.Font.Size = 12
            With .ChartArea.Font
                '.BaselineOffset = 0 'this doesn't work for me
                .Bold = msoFalse
                .Color = vbRed
                .Size = 10
            End With
        End With
    Next

End Sub

How to format font in all charts in chart group

1 Answers