VBA Conditional Formatting of Charts by Value
by Jon Peltier
Monday, March 3rd, 2008
Peltier Technical Services, Inc., Copyright © 2010.
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.
Related Posts:
- VBA Conditional Formatting of Charts by Category Label
- VBA Conditional Formatting of Charts by Series Name
- Pivot Table Conditional Formatting with VBA
- Conditional XY Charts Without VBA
- Split Data Range into Multiple Chart Series without VBA
- Conditional Stacked Clustered Column Chart with Targets
- Stack Columns In Order Of Size With VBA
- Ineffective Chart – Partition Chart
- Category Axis Tricks for Line and Area Charts – 1
- Extract Chart Data
Posted: Monday, March 3rd, 2008 under VBA.
Comments: 21
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 http://rossdillon.smugmug.com/photos/559288016_7kLZN-O-2.jpg.
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:
http://i40.tinypic.com/534wf7.png
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 SubI’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: http://i42.tinypic.com/2zp8uuq.png )
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
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.