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: 13
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.
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.