VBA to Set Uniform Colors for Clustered Column Chart Bars in Excel

Problem: I’m trying to automate the process of formatting a clustered column chart in Excel so that all bars within a cluster share the same color. By default, Excel assigns colors by series, which results in different colors for bars in the same cluster.

I need a VBA solution that:

Updates the data values without changing the colors, fonts, or labels.
Ensures that all bars in a given cluster have the same color (not by series, but by cluster).
Avoids manual formatting (I don’t want to click each bar twice and manually change colors).

What I’ve Tried:

Manually formatting the chart before updating values (Excel still overrides colors).
Using VBA to loop through series and apply colors (but this doesn’t group clusters correctly).
Searching online and testing multiple approaches, but none replicate the format I need.

Is there a reliable VBA method to control color assignment at the cluster level, rather than the series level? Or is Excel fundamentally unable to do this programmatically? Any guidance is appreciated!

Solution

Do you mean something like this?

Try the following code. You will probably have to tweak it a little bit so that it fits your need. Currently, it uses (only) 4 colors. Make up your mind how many different colors you need and define them. Also think about if you have only one chart or many and how and when you want to call the sub.

The code loops over all points of all series of a chart and sets the color.

FullSeriesCollection is the list of all series of a chart
Points are all points ("bars") within a series
The fill color of a point can be set using .Fill.ForeColor. If you also want to set the lines around the bar, use .Line.ForeColor

Sub setChartColors(ch As Chart)
    Dim colors(1 To 4) As Long
    colors(1) = RGB(255, 192, 0)
    colors(2) = RGB(48, 255, 48)
    colors(3) = RGB(220, 128, 192)
    colors(4) = RGB(0, 64, 255)
    
    Dim i As Long
    Dim ser As Series
    For Each ser In ch.FullSeriesCollection
        For i = 1 To ser.Points.Count
            Dim p As Point
            Set p = ser.Points(i)
            Dim colorIndex As Long
            colorIndex = ((i - 1) Mod UBound(colors)) + 1
            p.Format.Fill.ForeColor.RGB = colors(colorIndex)
            p.Format.Line.ForeColor.RGB = vbBlack
        Next i
    Next ser
End Sub

Format the current selected chart:

Sub ColorSelectedChart()
    Dim ca As ChartArea
    Debug.Print TypeName(Selection), TypeName(Selection.Parent)
    If TypeName(Selection) <> "ChartArea" Then Exit Sub
    
    setChartColors Selection.Parent 
End Sub

Format all charts of a specific sheet:

Sub ColorAllCharts()
    Dim co as ChartObject
    For each co in ThisWorkbook.Worksheets("Sheet1")
        setChartColors co.Chart
    Next
End Sub

Before:

After: