Color coding stacked bar chart using keys

Question

We want to create a stacked bar chart using keys to format the color of each field.

On the left, we have the keys and on the right, we have the length of each bar segment. Here is what we have right now.

The problem is that excel is not color coding the chart using keys assigned to it. Instead, it is just color coding them by row.

For example:
We need it to color code all administrative as red, introduction as yellow and so on. But in the current situation, excel is marking the first row as blue, second as yellow and so on.

Tim Williams Tim Williams · Accepted Answer · 2018-11-28T07:39:18

Example:

Sub ColorByCategory()

    Dim cht As Chart, s As Long, p As Long, cat

    Set cht = ActiveSheet.ChartObjects(1).Chart 'for example

    'here a series corresponds to a row of data...
    For s = 1 To cht.SeriesCollection.Count
        With cht.SeriesCollection(s)
            '...and points are grouped by data column
            For p = 1 To .Points.Count
                'find the category from the series and point numbers
                cat = Range("c4").Offset(s, (p - 1) * 2).Value
                .Points(p).Format.Fill.ForeColor.RGB = CatToColor(cat)
            Next p
        End With
    Next s

End Sub

'Map a category to a fill color using a lookup range
Function CatToColor(cat) As Long
    CatToColor = Sheet1.Range("A1:A5").Find(cat).Interior.Color
End Function

Color coding stacked bar chart using keys

1 Answers