Search code examples
vbaexcelexcel-2013

Changing Colors for a clustered bar graph based on axis label

Well I am trying to change the colors for my graph based on label conditions.

First of all, this is how the graph looks like. Graph Picture

I was trying to change the values based on three conditions. Warning, Critical and Emergency. Basically having three different colors for each of the conditions. For example all Emergencies be red, all critical be orange and all warning be yellow. "Title" is x-axis in my case and "criticality" happens to be the cluster.

I tried following the thread excel vba changing bar chart color for a data point based on point value

I also looked at this thread Changing Bar colors using VBA based on category label

I havent had any luck trying to use the code from both the threads.

This code from the second thread actually made sense for me to use in my case.

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

But not sure what variables should I be changing. Any help is welcomed!

Thanks.

Solution

  • the s.XValues(iPoint) will contain Title and Criticality concatenated. Try to change:

    If s.XValues(iPoint) = "avg"

    to:

    If right(s.XValues(iPoint), 9) = "Emergency"

    In this way you will check if the last 9 letters of the label are exactly "Emergency".

    You should be able to modify your code to add another condition for Critical or Warning.

    Hope this help,

    The Macro Guru