A color for each of the bar

[gamaz]

Hi,
I am recording a macro to generate a excel graph from a table as show below. Everything works fine. I need to use this code for automation purpose.

However the bar graph generated has only one color which is the default color blue. Is there any way for each of the four bars I can assign a color for each type of category

eg Total Green color, C/C Blue, CAR Yello and SCAR Gray.
I appreciate any help for resolution of this issue. Thanks

 
Sub Macro1()
'
' Macro1 Macro

'

'
    Range("A1:B5").Select
    Charts.Add
    ActiveChart.ChartType = xl3DColumnClustered
    ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:B5"), PlotBy:= _
        xlColumns
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = "Car Details"
        .Axes(xlCategory).HasTitle = False
        .Axes(xlSeries).HasTitle = False
        .Axes(xlValue).HasTitle = False
    End With
    ActiveWindow.Visible = False
    Windows("Book1").Activate
    Range("O30").Select
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.Walls.Select
    ActiveChart.ChartArea.Select
    ActiveWindow.Visible = False
    Windows("Book1").Activate
    Range("N32").Select
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.ChartArea.Select
    ActiveChart.ApplyDataLabels AutoText:=True, LegendKey:=False, _
        HasLeaderLines:=False, ShowSeriesName:=False, ShowCategoryName:=False, _
        ShowValue:=True, ShowPercentage:=False, ShowBubbleSize:=False
    ActiveChart.HasDataTable = True
    ActiveChart.DataTable.ShowLegendKey = True
    ActiveWindow.SelectedSheets.PrintPreview
    ActiveChart.SeriesCollection(1).Select
End Sub

CategoryCount
Total 44
C/C 17
CAR 12
SCAR 15

[Andy Pope]

    ActiveChart.ChartGroups(1).VaryByCategories = True