Excel – Changing Bar colors using VBA based on category label

excelvba

I have a VBA code in excel to change colors of bar graph but its not working for category series. 

ActiveChart.SeriesCollection(1).Interior.Color = RGB(0, 153, 64)

I want to change the color of a single bar. However, the above code changes the color of all bars.

For all bars I want one color (green) except for two bars representing two data points (Average1 and average2); these should be of a different color. Can anyone please tell me how to to this with VBA?

Best Answer

Jesse's answer is often the cleanest way to do it.

However, it is not accurate that "to have different colored bars they must be on different series" (my emphasis). You can mix and match colors within one series. For example, this makes the second bar of the first series red:

ActiveChart.SeriesCollection(1).Points(2).Interior.Color = RGB(255, 0, 0)

You can use this to do all kinds of neat tricks, such as highlighting bars that exceed some threshold, are associated with leap years, or whatever. You could certainly choose to highlight your average1 and average2 values this way.

If you want to change the color for a point that has a given characteristic, then you have to loop through all points until you find a point that has that characteristic. For example, if you want to color in red the point whose category (XValue) is "avg" then you could do this:

Dim c As Chart
Dim s As Series
Dim iPoint As Long
Dim nPoint As Long

Set c = ActiveChart
Set s = c.SeriesCollection(1)

nPoint = s.Points.Count
For iPoint = 1 To nPoint
    If s.XValues(iPoint) = "avg" Then
        s.Points(iPoint).Interior.Color = RGB(255, 0, 0)
    End If
Next iPoint
Related Topic