Sub FormattingCharts()
Dim myChart As Chart
Dim ws As Worksheet
Dim ax As Axis
Set ws = ThisWorkbook.Worksheets("Sheet1")
Set myChart = GetChartByCaption(ws, "GDP")
If Not myChart Is Nothing Then
Set ax = myChart.Axes(xlCategory)
With ax
.AxisTitle.Font.Size = 12
.AxisTitle.Font.Color = vbRed
End With
End If
Set ax = Nothing
Set myChart = Nothing
Set ws = Nothing
End Sub
Function GetChartByCaption(ws As Worksheet, sCaption As String) As Chart
Dim myChart As ChartObject
Dim myChart As Chart
Dim sTitle As String
Set myChart = Nothing
For Each myChart In ws.ChartObjects
If myChart.Chart.HasTitle Then
sTitle = myChart.Chart.ChartTitle.Caption
If StrComp(sTitle, sCaption, vbTextCompare) = 0 Then
Set myChart = myChart.Chart
Exit For
End If
End If
Next
Set GetChartByCaption = myChart
Set myChart = Nothing
Set myChart = Nothing
End Function