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

admin管理员组

文章数量:1352872

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!

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!

• excel
• vba
• charts

Share Improve this question Follow edited Apr 1 at 9:54 iFunction asked Apr 1 at 9:12 iFunctioniFunction 1,27977 gold badges2323 silver badges4141 bronze badges 3

• Could you add a picture of a chart to show how it should look like? – FunThomas Commented Apr 1 at 9:20
• Certainly as requested – iFunction Commented Apr 1 at 9:54
• Hi, I am unable to reply to people individually, thank you for your answers, I will be going through both of them as that is exactly what I am after. I will mark one of them as the correct answer of course. – iFunction Commented Apr 1 at 10:46

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 2

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:

Since Excel colors by series, you need to individually format each data point across series.

You can incorporate the below code in your existing vba code. Here is an example

Let's say my data looks like this

When the clustured chart is created, it looks like this

Now if you apply the below code, you will get what you want.

    Option Explicit

    Sub Sample()
        Dim chartObj As ChartObject
        Dim ch As Chart
        Dim s As Series
        Dim i As Long, j As Long
        Dim clusterColors As Variant
    
        '~~> Define your cluster colors here. I have done 3. Add more if you want
        clusterColors = Array(RGB(0, 176, 80), RGB(79, 129, 189), RGB(192, 80, 77))
    
        '~~> I am picking the chart from active sheet.
        '~~> You can use the chart that you created
        Set chartObj = ActiveSheet.ChartObjects(1)
        Set ch = chartObj.Chart
    
        '~> Loop through each category (i.e., cluster index)
        For i = 1 To ch.FullSeriesCollection(1).Points.Count
            '~~> Loop through each series and color the point
            For j = 1 To ch.FullSeriesCollection.Count
                ch.FullSeriesCollection(j).Points(i).Format.Fill.ForeColor.RGB = clusterColors(i - 1)
            Next j
        Next i
    End Sub

Output

本文标签： VBA to Set Uniform Colors for Clustered Column Chart Bars in ExcelStack Overflow