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
Set ax = myChart.Axes(xlValue)
With ax
.HasMinorGridlines = True
.MinorGridlines.Border.LineStyle = xlDashDot
End With
With myChart.PlotArea
.Border.LineStyle = xlDash
.Border.Color = vbRed
.Interior.Color = vbWhite
.Width = myChart.PlotArea.Width + 10
.Height = myChart.PlotArea.Height + 10
End With
myChart.ChartArea.Interior.Color = vbWhite
myChart.Legend.Position = xlLegendPositionBottom
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