Here is some code  I created a few years ago. I hope it may help


Worksheets("Sheet1").Select
Range("A10:A16,A18,N10:N16,N18").Select
Range("N18").Activate
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range( _
"A10:A16,A18,N10:N16,N18"), PlotBy:=xlColumns


ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Column
Chart"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Rental Agent's Closing
Percentages"
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
ActiveChart.HasLegend = False
ActiveChart.HasDataTable = False
ActiveChart.SeriesCollection(1).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.Shadow = False
Selection.InvertIfNegative = False
Selection.Fill.OneColorGradient Style:=msoGradientHorizontal,
Variant:=3, _
Degree:=0.231372549019608
With Selection
.Fill.Visible = True
.Fill.ForeColor.SchemeColor = 17
End With
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.Shadow = False
Selection.InvertIfNegative = False
Selection.Fill.OneColorGradient Style:=msoGradientHorizontal,
Variant:=3, _
Degree:=0.382345311665522
With Selection
.Fill.Visible = True
.Fill.ForeColor.SchemeColor = 17
End With