Changing color for Stacked bar chart...

[kjicha]

I have 4 columns Data with the following data

Category, Description, Start Date, Duration (in days)
ABC ABC - Test1 3/1/2016 80
DEF DEF - Test2 1/1/2014 1117
GHI GHI - Test3 2/5/2015 500
JKL JKL - Test4 8/1/2020 400

I have put this data into a Stacked Bar chart, so it looks like a gantt chart. What I am trying to do is I want the bars for ABC - Test1 to show up as Green if Category equals ABC for that description. Then a different color for the next one and so on. I can't seem to figure out how to have the bar colors change automatically. I will take any help I can get, thank you in advance.

 

[pbornemeier]

Option Explicit

Sub ChangeDataPointColor()

    Dim ser As Series
    Dim pt As Point
    Dim sTriggerSeries
    Dim sTriggerCategory
    Dim bShowingValue As Boolean
    Dim bShowingCatName As Boolean
    Dim bShowingSeriesName As Boolean
    
    sTriggerSeries = "ABC - Test1"
    sTriggerCategory = "ABC"
    
    For Each ser In ActiveChart.SeriesCollection
        If ser.Name = sTriggerSeries Then
            'Save Data Label Config
            If ser.HasDataLabels Then
                bShowingValue = ser.DataLabels.ShowValue
                bShowingCatName = ser.DataLabels.ShowCategoryName
                bShowingSeriesName = ser.ShowSeriesName
            Else
                ser.ApplyDataLabels
            End If
            
            'Set Data Labels as required
            ser.DataLabels.ShowValue = False
            ser.DataLabels.ShowCategoryName = True
            ser.ShowSeriesName = False
            
            'Check Point Category names
            For Each pt In ser.Points
                If pt.DataLabel.Text = sTriggerCategory Then
                    pt.Format.Fill.ForeColor.RGB = rgbGreen
                End If
            Next
            
            'Restore Data Labed Config
            ser.DataLabels.ShowValue = bShowingValue
            ser.DataLabels.ShowCategoryName = bShowingCatName
            ser.ShowSeriesName = bShowingSeriesName
            
        End If
    Next
    
End Sub

 

[kjicha]

Thank you very much