VBA Conditional Formatting of Charts by Value

by Jon Peltier
Monday, March 3rd, 2008
Peltier Technical Services, Inc., Copyright © 2012.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.

I’ve got a couple of tutorial pages on my web site that deal with conditionally formatted charts. The trick is to set up ranges containing formulas for each of the conditions, so that if the condition for that range is met, the range contains the value to plot, otherwise it contains #N/A, which isn’t plotted in a line or XY chart, or just a blank or zero, which is plotted as a zero-thickness bar or column in a bar or column chart. You can read about them here:

Simple Conditional Chart Example 1 – Simple Conditional Chart Example 2

As with conditional formatting in the worksheet, the formatting you can achieve is limited only by your ability to construct formulas. Of course, in the chart, you are not limited to three conditional formats as you are in the worksheet (in Excel 2003 at least; the conditional formatting enhancements in Excel 2007 are mind-boggling).

Although I prefer to use the formulaic approach above, sometimes it makes more sense to use VBA to apply formats. Suppose you have a column chart, and the values may be in any order. However, you want to color a column according to its value, so that small values always are colored red and large values are always green.

The following protocol allows you to color the points in a series according to colors you’ve reserved for certain values. The range below illustrates the data: range A1:A4 contains a list of the categories, with each cell filled with the desired color for that value in a chart; actually, the procedure will use the color in the cell with the smallest value greater than or equal to the point’s value. A6:B10 contains data for Chart 1, and A12:B16 contains data for Chart 2. (The colors in A1:A4 were defined using the Color Brewer utility described in my earlier post, Using Colors in Excel Charts.)

The charts made from the two ranges are not remarkable.

There is a simple VBA procedure that will apply the colors from the designated range to the appropriate points, based on their categories:

Sub ColorByValue()
  Dim rPatterns As Range
  Dim iPattern As Long
  Dim vPatterns As Variant
  Dim iPoint As Long
  Dim vValues As Variant
  Dim rValue As Range

  Set rPatterns = ActiveSheet.Range("A1:A4")
  vPatterns = rPatterns.Value
  With ActiveChart.SeriesCollection(1)
    vValues = .Values
    For iPoint = 1 To UBound(vValues)
      For iPattern = 1 To UBound(vPatterns)
        If vValues(iPoint) <= vPatterns(iPattern, 1) Then
          .Points(iPoint).Interior.ColorIndex = _
              rPatterns.Cells(iPattern, 1).Interior.ColorIndex
          Exit For
        End If
      Next
    Next
  End With
End Sub

Select a chart, and run the procedure above (press Alt+F8 to bring up the macro dialog, select the procedure, and click Run). The bars will change color according to their values and the colored table in A1:A4.

To format by category label, see VBA Conditional Formatting of Charts by Category Label.
To format by series name, see VBA Conditional Formatting of Charts by Series Name.

Posted: Monday, March 3rd, 2008 under VBA.
Comments: 37

Comments

Comment from Valerie
Time: Monday, June 1, 2009, 10:53 am

I’m trying to create bar graphs where the bar (for each month of the year x axis) changes color (green above flat budget $/unit) or (red below flat budget $/unit). Does anyone know how to do this? I also have a another chart where the budget volume is different by month and would like the bar for actual volume by month to change to red or green depending if the actual volume is higher/lower than the budget volume for the month. Any suggestions would be appreciated.

Comment from Jon Peltier
Time: Monday, June 1, 2009, 10:57 am

Valerie -

If the green/red transition occurs at zero, you could try Invert if Negative. If the transition is at a non-zero value, which sounds like your case, you can try Simple Conditional Charts.

Comment from Ross Dillon
Time: Tuesday, June 9, 2009, 4:22 pm

Please refer to this image [image no longer available].

Some points about the graph before the question. It’s critical for my purpose that each slice appear to be 1/8 of each ring (each represent an azimuth angle around an airplane). The trick was to set the value of each cell to “1″ plus the actual value divided by 1000. That makes the difference between slices so minimal that the rings appear to be equally spaced. The hope is to then shade each slice as shown in range (I1:I4) which was the only change I made to the VBA subroutine

I also rotated the data so the zero azimuth is pointed to the right, and X-1 is the inner ring.

I’m using this routine with the referenced donut chart and it runs OK but only changes the colors of the inner ring (I think that’s because of the line “With ActiveChart.SeriesCollection(1)” which I don’t recall how to fix to include every SeriesCollection). But, the colors did not match the defined region (I1:I4) nor did they follow any pattern regarding the values.

One thing that has me concerned is after I started I found in the Help file that this works for graphs such that “You don’t have more than seven categories per data series” which I do…but the chart was created successfully. Why this restriction? What does in impact (since I created one OK)?

Help?

Comment from Jon Peltier
Time: Tuesday, June 9, 2009, 4:57 pm

Ross -

You need another link to loop through each series:

Sub ColorByValue()
  Dim rPatterns As Range
  Dim iPattern As Long
  Dim vPatterns As Variant
  Dim iPoint As Long
  Dim vValues As Variant
  Dim rValue As Range
  Dim srs As Series

  Set rPatterns = ActiveSheet.Range("A1:A4")
  vPatterns = rPatterns.Value
  For Each srs In ActiveChart.SeriesCollection
    With srs
      vValues = .Values
      For iPoint = 1 To UBound(vValues)
        For iPattern = 1 To UBound(vPatterns)
          If vValues(iPoint) <= vPatterns(iPattern, 1) Then
            .Points(iPoint).Interior.ColorIndex = _
                rPatterns.Cells(iPattern, 1).Interior.ColorIndex
            Exit For
          End If
        Next
      Next
    End With
  Next
End Sub

If the color index commands don’t work, you could try replacing .ColorIndex by .Color (color index worked for me).

Comment from Ross Dillon
Time: Tuesday, June 9, 2009, 5:12 pm

Thanks, I figured that would do it but while I’m good at reading code, I’m not so good at creating it!

As for the colors, It’s only the A4 green that doesn’t come out correctly. So I created a green default template chart and ingore the forth loop (if it belongs green, it won’t change it). Reduces run time by 25% also :)

Comment from Jon Peltier
Time: Tuesday, June 9, 2009, 9:33 pm

That could be made faster by turning off screen updating while it works:

Sub ColorByValue()
  Dim rPatterns As Range
  Dim iPattern As Long
  Dim vPatterns As Variant
  Dim iPoint As Long
  Dim vValues As Variant
  Dim rValue As Range
  Dim srs As Series

  Application.ScreenUpdating = False
  Set rPatterns = ActiveSheet.Range("A1:A4")
  vPatterns = rPatterns.Value
  For Each srs In ActiveChart.SeriesCollection
    With srs
      vValues = .Values
      For iPoint = 1 To UBound(vValues)
        For iPattern = 1 To UBound(vPatterns)
          If vValues(iPoint) <= vPatterns(iPattern, 1) Then
            .Points(iPoint).Interior.ColorIndex = _
                rPatterns.Cells(iPattern, 1).Interior.ColorIndex
            Exit For
          End If
        Next
      Next
    End With
  Next
  Application.ScreenUpdating = True
End Sub

Comment from Jason Peters
Time: Tuesday, November 24, 2009, 3:34 am

Hi there,

I want to modify the conditional formatting code by changing teh colour of the graphs not by the value of the bar but by another field.

The values each bar should be coloured is located on another worksheet (Worksheet X) in column N. My question is how do i modify the below code to not take graph values as the colour but take the values in column N on worksheet X.

Sub ColorbyValue()
Dim rPatterns As Range
Dim iPattern As Long
Dim vPatterns As Variant
Dim iPoint As Long
Dim vValues As Variant
Dim rValue As Range
Dim srs As Series

Application.ScreenUpdating = False
Set rPatterns = ActiveSheet.Range(“A1:A6″)
vPatterns = rPatterns.Value

For Each srs In ActiveChart.SeriesCollection
With srs
vValues = .Values
For iPoint = 1 To UBound(vValues)
For iPattern = 1 To UBound(vPatterns)
On Error Resume Next
If vValues(iPoint) <= vPatterns(iPattern, 1) Then
.Points(iPoint).Interior.ColorIndex = _
rPatterns.Cells(iPattern, 1).Interior.ColorIndex
On Error GoTo 0
Exit For
End If
Next
Next
End With
Next
Application.ScreenUpdating = True
End Sub

Comment from Jon Peltier
Time: Tuesday, November 24, 2009, 7:52 am

Change the vValues definition to:

    vValues = Worksheets("X").Range("N1:N100").Value

Adjust the address N1:N100 to the range with the values of interest.

Comment from Jason Peters
Time: Tuesday, November 24, 2009, 9:54 am

Hi Jon!

Thanks mate, but the code only seems to colour the graphs with the first colour and ignores all other values with different associated colours. So if my values are

1 = red
2 = yellow
3 = green

The code colours all bars red, even if they have a value of 2 or 3!

Comment from Jon Peltier
Time: Tuesday, November 24, 2009, 10:55 am

Jason -

Is the range address in the vValues = statement correct?
Are the values in that range numerical? Text is interpreted as zero.

Did you run this with no error? I should have changed another line:

    If vValues(iPoint, 1) < = vPatterns(iPattern, 1) Then

Comment from Jason Peters
Time: Tuesday, November 24, 2009, 10:34 pm

oh thanks mate!! that other line ammendment fixed it. really appreciate your help buddy!

Comment from Dean
Time: Thursday, January 21, 2010, 6:08 am

Hi Jon,

I am looking to color data points based upon the quadrant in which they are present (therefore this is an xy scatter) with the intercept at the average value of x, so therefore the colour will be bases upon 1. a +ve or -ve number on one axis, and 2. the presence above or below the mean of the x axis, any help would be most appreciated

Dean

Comment from Jon Peltier
Time: Thursday, January 21, 2010, 8:56 am

Dean -

Good question. I’ve answered it in a new post, Conditional XY Charts Without VBA.

Comment from Shane
Time: Tuesday, February 16, 2010, 5:33 am

Thanks for the great info you have on this site, I found some very interesting stuff on here. Sure I will use some of it at some point.

Regards

Shane

Comment from tyler
Time: Tuesday, February 16, 2010, 10:40 am

i’m trying to get a button set up so I don’t have to go through the developer tab each time and run the macro, but when I try to do this, i get an error for Object Variable or With block variable not set, so it’s like i’m not clicking on the chart that I want to update. Is there a way that can set up my VBA so that I don’t have to be clicked inside of the chart?

Comment from Jon Peltier
Time: Tuesday, February 16, 2010, 8:26 pm

Tyler -

If there’s no other chart on the sheet, try the code below. If there are multiple charts, how would the program know which one to change?

Sub ColorByValue()
  Dim rPatterns As Range
  Dim iPattern As Long
  Dim vPatterns As Variant
  Dim iPoint As Long
  Dim vValues As Variant
  Dim rValue As Range

  Set rPatterns = ActiveSheet.Range("A1:A4")
  vPatterns = rPatterns.Value
  With ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1)
    vValues = .Values
    For iPoint = 1 To UBound(vValues)
      For iPattern = 1 To UBound(vPatterns)
        If vValues(iPoint) <= vPatterns(iPattern, 1) Then
          .Points(iPoint).Interior.ColorIndex = _
              rPatterns.Cells(iPattern, 1).Interior.ColorIndex
          Exit For
        End If
      Next
    Next
  End With
End Sub

Comment from Vincent
Time: Tuesday, April 13, 2010, 10:57 am

Hi Jon,

I have a question regarding conditional formatting bar charts. I’ve tried your examples, but I don’t get them to work for me. I Hope you can help.

I have a series of bar charts that I want to change from red to green if they are greater than / equal to or less than a certain value. I have uploaded a picture to show you the setup:

[image no longer available]

What I want to do is change the color to red if the value is less than the norm. Green if the value is equal to or greater than the norm.

This graph is different from the setup in your examples for the simple conditional format and I have tried a lot of things but I think I don’t have enough experience to get this to work without any help.

Thanks for your answer!

Comment from Vincent
Time: Tuesday, April 13, 2010, 11:00 am

I’m sorry but I just found out that I did not set the colors right in the example picture. The second bars should be green, red, red, green and red.

Comment from Jon Peltier
Time: Tuesday, April 13, 2010, 11:26 am

Vincent -

The examples here are to provide insight into concepts, but often the implementation needs a different algorithm.

In this article, the algorithm simply loops through the values of every point in the series, compares that value to a table, and applied the appropriate color to the corresponding data point.

Your situation needs a different approach:
A point is labeled only if it is the value for a product, not for a norm. This means if the point has a zero value, don’t label it, because it’s not a product’s point, and if the previous point has zero value, don’t label this point either because the previous point isn’t a norm. If it’s okay to label the point, compare its value to the previous value, and apply either green or red fill.

Something like this:

Sub DoColor()
  Dim vPatterns As Variant
  Dim iPoint As Long
  Dim vValues As Variant
  Dim rValue As Range

  With ActiveChart.SeriesCollection(1)
    vValues = .Values
    For iPoint = 2 To UBound(vValues)
      If vValues(iPoint) > 0 And vValues(iPoint - 1) > 0 Then
        If vValues(iPoint) >= vValues(iPoint - 1) Then
          .Points(iPoint).Interior.Color = RGB(0, 255, 0)
        Else
          .Points(iPoint).Interior.Color = RGB(255, 0, 0)
        End If
      End If
    Next
  End With
End Sub

I’m not going to try here to reproduce your gradient fill. You can record a couple macros in 2003 to get the additional syntax you need for that.

Comment from Vincent
Time: Tuesday, April 13, 2010, 11:43 am

Hi Jon,

Thank you for your quick answer! I will get into VBA a little more (I am a novice) as I understand that none of the simple conditional formats will work for my situation.

Maybe I will just get rid of the gradients to make things easier.

If I don’t get it to work I may come back and ask you a question, but not before I gain knowledge about VBA.

(Updated picture: [image no longer available])

Comment from Vincent
Time: Monday, April 19, 2010, 7:54 am

Hi Jon,

Thanks again for your help. I read a lot about VBA last week and I’m beginning to understand how it works now. For a novice like me it is quite hard understanding the object-model. I don’t know where to find good information to start programming with VBA, but with al little Google and some code from others (and a lot from you) I made this working macro for all the charts on the active worksheet. I even managed to assign it to a button on the page. It works great! :-)

Sub AdjustChartColor()
‘ Macro created on 2010-04-19 by Vincent
‘ with help from Jon Peltier: http://www.peltiertech.com

Dim cObject As ChartObject
Dim iPoint As Long
Dim vValues As Variant

For Each cObject In ActiveSheet.ChartObjects
With cObject.Chart.SeriesCollection(1)
vValues = .Values
For iPoint = 2 To UBound(vValues)
If vValues(iPoint) > 0 And vValues(iPoint – 1) > 0 Then
If vValues(iPoint) >= vValues(iPoint – 1) Then
.Points(iPoint).Fill.ForeColor.SchemeColor = 4
Else
.Points(iPoint).Fill.ForeColor.SchemeColor = 3
End If
End If
Next
End With
Next
End Sub

Comment from Leo
Time: Tuesday, November 2, 2010, 1:38 pm

Hi Jon,

Thank you for this brilliant site of yours!
I’m trying to use the proposed code above but with some slight changes:
1) I want to change the color of the bars (datapoints) depending on the name of the point (xvalue) and not the value
2) I want to the bar to get the same color as the cell I’m pointing at in the VBA code (ie. the vValue = range)
3) the Cells I’m pointing at are formated through conditional formatting.

I believe I have managed to get 1) and 2) to work but it only changes the bar colors to white (none color)…?! I think this might be because of the conditional formatting.

Any advise?
Thanks for your answer!

Regards,
Leo

Comment from Leo
Time: Tuesday, November 2, 2010, 2:39 pm

To be more precise I use the following code.
Sub Color_change()

Dim serX As Series
Dim vntNames As Variant
Dim lngPoint As Long
Dim lngIndex As Long
Dim rPatterns As Range
Dim vPatterns As Variant

Set rPatterns = Worksheets(“Graph input”).Range(“E21:E50″)
vPatterns = rPatterns.Value
With ActiveChart.SeriesCollection(1)
vntNames = .XValues
For lngIndex = LBound(vntNames) To UBound(vntNames)
lngPoint = lngPoint + 1
.Points(lngPoint).Interior.ColorIndex = rPatterns.Cells(lngPoint, 1).Interior.ColorIndex
Next
End With
End Sub

It works fine if I manually set the background color in the cells but not if they are conditionally formated

Comment from Jon Peltier
Time: Wednesday, November 3, 2010, 2:00 pm

Leo -

It’s not easy to apply a cell’s conditional formatting to a chart element. The cell retains its original formatting, with the conditional formatting overruling it. When you query the format of the cell, you only see the original format. You need first to find out which condition is met, then find out what the applicable format is for that format. This is the format to apply to the points.

Comment from Leo
Time: Friday, November 5, 2010, 6:49 am

Hi Jon,

Thanks a lot for you comments.

I have tried to do a work around bu can’t manage getting it to work.
Basically what I’m trying to do is to change the colors of each data point in a bar chart depending on its name (XValues).

I have in the input sheet the following columns

Name Type

Volvo Car
BMW Car
Boening Airplane
Zodiac Boat

Type definition column:
Car
Boat
Airplane

I want the code to do the following (pseudo code):
1) compare each data point in the bar chart with the name column
2) if they are equal:
3) compare the typ column with the typ_definition_column
4) if they are equal
5)set interior.color = RGB(x,y,z)

I coded the following but can’t get it to work (Nothing happens).
Could you possibly point me out into the right direction?

Once again thans for a great site!

/Leo

Sub ColorByXValue()

Dim rName As Range
Dim iName As Long
Dim vName As Variant

Dim rGroup As Range
Dim vGroup As Variant

Dim iPoint As Long

Dim vChrValues As Variant
Dim rChrValue As Range

Dim rGroupType As Range
Dim vGroupType As Variant

Set rName = Worksheets(“Graph input”).Range(“F21:F50″)
vName = rName.Value

Set rGroupType = Worksheets(“Graph input”).Range(“G52:F56″)
vGroupType = rGroupType.Value

Set rGroup = Worksheets(“Graph input”).Range(“H21:H50″)
vGroup = rGroup.Value

With ActiveChart.SeriesCollection(1)
vChrValues = .XValues
For iPoint = LBound(vChrValues) To UBound(vChrValues)
For iName = 1 To UBound(vName)
If vChrValues(iPoint) = vName(iName, 1) Then
If vGroup(iName, 1) = vGroupType(1, 1) Then
.Points(iPoint).Interior.Color = RGB(56, 46, 25)
ElseIf vGroup(iName, 1) = vGroupType(2, 1) Then
.Points(iPoint).Interior.Color = RGB(113, 93, 50)
ElseIf vGroup(iName, 1) = vGroupType(3, 1) Then
.Points(iPoint).Interior.Color = RGB(204, 191, 142)
ElseIf vGroup(iName, 1) = vGroupType(4, 1) Then
.Points(iPoint).Interior.Color = RGB(229, 219, 186)
End If
End If
Next
Next
End With
End Sub

Comment from Jon Peltier
Time: Friday, November 5, 2010, 1:09 pm

Leo -

Step through the code, and make sure each of the arrays contains the data you expect it to have.

Comment from Leo
Time: Monday, November 8, 2010, 9:13 am

Thanks Jon!

After some struggling I finally got it to work.
It seems as chart objects in Excel add an extra space after each data point name (XValues) i.e. if the name of the bar is “Volvo” the value is “Volvo “!

Once again thanks for you help!
/Leo

Comment from Jon Peltier
Time: Monday, November 8, 2010, 10:22 am

Leo -

This mismatch probably is more a function of where the data was extracted from. Excel doesn’t just arbitrarily append spaces to labels.

Comment from David
Time: Wednesday, May 4, 2011, 5:20 am

Hi John,

Greatly appreciate the posting of this code to set us on our way.

I have been trying to apply it an example not markedly different to your sample but for the rPatterns is a horizontal – not vertical – range (say A1:D1). The macro is only changing the first datapoint. Stepping through the code in the Locals Window I notice that the UBound(vPatterns) value is 1 not 4. Do I need to amend the code for an Array orientation change? Also, does the rValue variable serve a purpose?

Thank you,
David

Comment from Jon Peltier
Time: Wednesday, May 4, 2011, 3:44 pm

David -

Strictly speaking, the UBound command should have been

For iPattern = 1 To UBound(vPatterns, 1)

since it needed the number of rows, but by default, if you leave off the second parameter, VBA assumes 1. We need a few adjustments to query a horizontal range:

For iPoint = 1 To UBound(vValues)
  For iPattern = 1 To UBound(vPatterns, 2)
    If vValues(iPoint) < = vPatterns(iPattern, 2) Then
      .Points(iPoint).Interior.ColorIndex = _
          rPatterns.Cells(iPattern, 2).Interior.ColorIndex
      Exit For
    End If
  Next
Next

Comment from David
Time: Thursday, May 5, 2011, 3:30 am

Jon,

Greatly appreciate your explanation.

Thank you,
David

Comment from Mikhail
Time: Sunday, August 21, 2011, 1:32 am

Hi John,

I have similar question about formatting the graph. If you would help me with that I would greatly appreciate it.

I have a sheet that contains 3 columns and 20 rows. First column contains the text in each cell (company names). The second and third columns contain numbers related to the company name in the first column. I would like to assign each name its specific colour, so when I change the data, the VB recognizes the company name and changes the colour of the first and second points (which are in the 1st and 2nd columns) on the graph.

Could you, please point me into the right direction?

Thank you in advance!

Comment from Sekiya-Nanny
Time: Friday, December 16, 2011, 12:44 am

Thank you so much for these fantastic ideas.

Sekiya-Nanny

Comment from Julie
Time: Tuesday, January 10, 2012, 6:24 pm

This has been a tremendous help! The only question I have is when I have two charts on the page it changes both of them – is there a way around this? (I’ve modified the stuff Vincent worked on with you).
Sub AdjustChartColor()
‘ Macro created on 2010-04-19 by Vincent
‘ with help from Jon Peltier: http://www.peltiertech.com

Dim cObject As ChartObject
Dim iPoint As Long
Dim vValues As Variant

For Each cObject In ActiveSheet.ChartObjects
With cObject.Chart.SeriesCollection(1)
vValues = .Values
For iPoint = 1 To UBound(vValues)
If vValues(iPoint) > 50 Then
.Points(iPoint).Fill.ForeColor.SchemeColor = 2
Else
.Points(iPoint).Fill.ForeColor.SchemeColor = 6
End If
Next
End With
Next
End Sub

Comment from Gjergji Spaho
Time: Monday, January 30, 2012, 11:53 am

Hi,
I found the article of great help.

However I’m having trouble with the Reset To Match Style command; once you have clicked over the chart then the VBA code does not affect the chart anymore.

The only workaround is to select manually a series, bring up the Formatting popup and change anything in there – then again the code works but only for the series I changed something.

I’m using Office 2010; any suggestion would be of a great help.

thanks, Gjergji

Comment from Jon Peltier
Time: Monday, January 30, 2012, 12:25 pm

Gjergji -

That’s strange, somehow the Reset to Match Style must lock the chart formatting. I’ve never used this feature, since there’s no style I particularly want to match.

What are you using this for? Could you do the style matching manually, so the chart’s formatting isn’t locked?

Comment from Gjergji Spaho
Time: Tuesday, January 31, 2012, 4:15 am

Hi John,
there is no particular importance from the usage point of view.
I came across it while experimenting formatting the chart with the VBA code and it was the only way I found to reset it to the initial state.

I was wondering if there was a way to circumvent it.

thanks, for your help and reply
best regards, Gjergji

Write a comment

I welcome comments from my readers. If you have an opinion on this post, if you have a question or if there is anything to add, I want to hear from you. Whether you agree or disagree, please join the discussion.

If you want to include an image in your comment, post it on your own site or on one of the many free image sharing sites, and include a link in your comment. I'll download your image and insert the necessary html to display the image inline.

Read the PTS Blog Comment Policy.

Excel Chart Add-Ins | Training | Charts and Tutorials | Peltier Tech Blog

Main menu:

Excel Dashboards

Amazon Books

Subscribe

Site search

Subscribe

Site search

Recent Posts

Popular Posts

Categories

VBA Conditional Formatting of Charts by Value

Related Posts:

Comments

Write a comment