Assigning chart colors dynamically

[rovinrail]

I am charting off of a basic query-- work value by month for multiple locations (A-L, 12 different locations, all in the same column of data). The problem I'm having is locking in the colors for the chart-- Location "A" should always be red, and Location "B" always blue, for example. The data doesn't always have all 12 locations (for example, one report may only have A,C,H,J), so I can't let it just auto-select the colors or order them descending or whatever.

I've tried any number of the google-found permutations of vba coding, and I still can't find one that actually works. (example from the table I'm charting off of below)

Can someone help me stop banging my head on my desk? Thanks!

Location Work Month
A 2018.42 1-Jan-12
A 1712.27 1-Apr-11
A 1861.97 1-Apr-12
Location Work Month
B 1340.96 1-May-12
B 1387.22 1-May-11
C 1059.67 1-Jan-12
C 873 1-Apr-11
C 1141.23 1-Apr-12

 

[pbornemeier]

What type of chart are you using? The code is a bit different between pie, bar and line charts.
What version of Excel are you using?
Where does the date appear on the chart?
Where does the location appear on the chart?

 

[pbornemeier]

This will work for a bar or column chart where the Location is the category name:

Option Explicit

Sub ColorChartByCategoryNames()
    'Works for Bar Chart with A, B, C as category names
    
    Const lLocAColor As Long = 255 'Red
    Const lLocBColor As Long = 16711680 'Blue
    Const lLocCColor As Long = 32768 'Green
    
    Dim vCatNames As Variant
    Dim lX As Long
    
    ActiveSheet.ChartObjects(1).Activate
    ActiveChart.SeriesCollection(1).Select

    vCatNames = ActiveChart.Axes(xlCategory).CategoryNames
    With ActiveChart.SeriesCollection(1)
        For lX = 1 To .Points.Count
            Select Case vCatNames(lX)
            Case "A": .Points(lX).Interior.Color = lLocAColor
            Case "B": .Points(lX).Interior.Color = lLocBColor
            Case "C": .Points(lX).Interior.Color = lLocCColor
            End Select
        Next
    End With
    
End Sub