I’ve got a number of tutorials on this blog 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.
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.
Excel 2003 and earlier:
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
Excel 2007 and later:
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).Format.Fill.ForeColor.RGB = _
rPatterns.Cells(iPattern, 1).Interior.Color
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.
Related Articles about VBA Conditional Formatting of Charts
- VBA Conditional Formatting of Charts by Series Name
- VBA Conditional Formatting of Charts by Category Label
- VBA Conditional Formatting of Charts by Value and Label
- Conditional Formatting of Lines in an Excel Line Chart Using VBA
Valerie says
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.
Jon Peltier says
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.
Ross Dillon says
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?
Jon Peltier says
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 SubIf the color index commands don’t work, you could try replacing .ColorIndex by .Color (color index worked for me).
Ross Dillon says
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 :)
Jon Peltier says
That could be made faster by turning off screen updating while it works:
Jason Peters says
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
Jon Peltier says
Change the vValues definition to:
vValues = Worksheets("X").Range("N1:N100").ValueAdjust the address N1:N100 to the range with the values of interest.
Jason Peters says
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!
Jon Peltier says
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:
Jason Peters says
oh thanks mate!! that other line ammendment fixed it. really appreciate your help buddy!
Dean says
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
Jon Peltier says
Dean –
Good question. I’ve answered it in a new post, Conditional XY Charts Without VBA.
Shane says
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
tyler says
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?
Jon Peltier says
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?
Vincent says
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!
Vincent says
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.
Jon Peltier says
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:
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.
Vincent says
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])
Vincent says
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://972.86c.myftpupload.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
Leo says
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
Leo says
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
Jon Peltier says
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.
Leo says
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
Jon Peltier says
Leo –
Step through the code, and make sure each of the arrays contains the data you expect it to have.
Leo says
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
Jon Peltier says
Leo –
This mismatch probably is more a function of where the data was extracted from. Excel doesn’t just arbitrarily append spaces to labels.
David says
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
Jon Peltier says
David –
Strictly speaking, the UBound command should have been
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:
David says
Jon,
Greatly appreciate your explanation.
Thank you,
David
Mikhail says
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!
Sekiya-Nanny says
Thank you so much for these fantastic ideas.
Sekiya-Nanny
Julie says
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://972.86c.myftpupload.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
Gjergji Spaho says
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
Jon Peltier says
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?
Gjergji Spaho says
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
Rachel Smith says
Hi
I am struggling with my pivot chart. Basically depending on the value I want the colour of the bar to change
>= 95 – Green
>=85 and < 95 – Orange
95, <95 and < 85. All columns are setting to Green. Are the values in my range the problem?
Many thanks
Rachel
Jon Peltier says
Rachel –
I think you’re better off using the approach in Conditional Formatting of Excel Charts. It doesn’t use code, and there’s no issues with the weirdness of the Excel 2007 color system.
Katie says
Hi,
I am very new to VBA and this has been amazingly instructive & helpful!!
I would love to be able to adapt the original code so that I can use a different range to assign the correct colour for each different chart point in my pie chart. I don’t know how to select each point individually and then move on to the next one, rather than loop through all of the points assessing them all against the same range. Can you help me?
Thanks so much!!
Katie
Jon Peltier says
Katie –
You might be trying to squeeze more meaning into your pie chart than is possible to extract. But anyway, what you want is possible, though it takes a more complicated algorithm. Hang tight and I’ll write it up in a new post.
Katie says
Thanks Jon,
What I am attempting to do is to automate a sort of supplier KPI “steering wheel”, so each segment of my pie chart, whilst sized according to weighting rather than value, is scored individually and against differing criteria. If I can get this working it will save hours and hours every month!!
Many many thanks,
Katie
Annamaria R says
Hello,
When I duplicate the example you have above, I am able to get the VBA to work..however when I try and use it on my spreadsheet, it is not working, and I think it has something to do with the fact that I have multiple series, as well as the chart in a different tab. Help?
Thanks in advance,
Annamaria
Jon Peltier says
Annamaria –
You have to make some adjustments to the code. It’s probably easiest to select the chart on its sheet before running the code, so you need to specify the sheet that contains the formatted range. Also you need to cycle through the series in the chart.
Annamaria R says
Thank you so much Jon!! This works perfectly.
Matthew H says
Jon,
This code is fantastic, I’m using the version adapted for Ross, earlier in the comments (for multiple series). I am using this on a 3D column grid of around 60×100 cells. It works but is very slow and freezes excel for awhile, do you have any tips on how it could be sped up? I do have a number of blank cells, is there any way to make the macro skip these cells to make it faster.
Any ideas much appreciated!
Thanks again for your help.
Jon Peltier says
Matthew –
Are you using the second version for Ross, with Application.ScreenUpdating changed to False at the beginning and back to True at the end?
Jon Peltier says
Matthew replied off-line:
Yes, I’m using that version with screenupdating false then true. It still takes a couple of minutes to refresh and excel freezes in the meantime.
If I reduce the grid size to half, it is noticeably quicker but still lags significantly. My PC is not particularly underpowered.
Any ideas?
My follow-up:
Which version of Excel? How many datapoints do you have?
2007 is particularly slow, especially with more than a couple hundred points.
Chris says
John,
These articles your write are incredibly useful. Thank you very much for sharing your expertise.
Just to build on this methodology, users can use this for colouring doughnut charts too. Whilst they may not be such good charts, management do seem to like them, especially for traffic light type colouring of key metrics.
As an example, say you have one overall KPI – “customer satisfaction” and that KPI has 6 sub components. the VBA method coems in really handy if each sub component has a different weighting within the overall KPI, say;
1. website experience is worth 50% of our overall customer sat KPI
2. delivery time is worth 25% of the overall customer sat KPI
3-6 same thing for the remaining 4 sub components so that you get to 100% in total
What we want is a doghnut chart for the customer satisfaction KPI with each sub component highlighted in either red, green or amber depending on how actuals are v’s the targets we set for each of those sub components.
In the data we plot the doughnut chart as per the percentages in items 1-6, so website experience is 0.5, delivery time 0.25 etc. that draws the doughnut with the subcomponents in the correct proportions.
Also create three cells e.g. A1:A3 with your traffic light colouring in e.g. A1, enter cell value 1 and colour green, A2 enter cell value 2 and colour amber and A3 enter cell value 3 and colour red. This sets up your traffic lights.
Now all we need is a formula in the column next to where we had the proportions data. All this formula does is compare our actual outturn with the targets set and if better than target inserts value 1, if under target inserts 3 and if within a set rnage around the target would insert 2. Those ranges are dependent upon the targets set by the organisation.
Then using the VBA posted by Jason Peters and updated by you you can tell the doughnut chart to apply the formatting in the cells A1:A3 to the values you created in the formula cells and hey presto a chart in the right proportions and coloured in the traffic light style for your KPI.
Jon Peltier says
Chris –
You mention multiple worst practices in your comment.
1. Donut charts, for reasons we’ve been over.
2. Red-Yellow-Green color scales, for color-related issues. What if you have to photocopy or fax the printout? Or, what if the viewer is among the >8% of men who have red-green color perception difficulties?
3. Something as easy as pie or donut wedges, and even bar charts, can be set up to change color without code.
Chris says
John, I agree wholeheartedly with your comments around the structure of the charts. Unfortunately we sometimes have to play the hand we are dealt until we can get people to listen to our recommendations.
Louise says
Jon,
Thank you for your very helpful articles. These seem to be addressing a problem very similar to the one I am trying to solve. I have several series of data types from an experiment that cycles through a loop. I then plot the data and each loop lies nearly on top of the first but the differences are important. I would like to colour code a scatter plot with smooth lines to show a different colour for each of the cycles. My data has a column with the cycle number in (1,2,3, etc) and then several more columns with voltage, current etc. so for example I would like to plot voltage against current in a scatter plot with smooth lines where the colour depends on the number in the cycle column. Since I am plotting the data several different ways separating the data out into different columns is possible but very cumbersome, any help with a VBA alternative would be greatly appreciated.
Jon Peltier says
Louise –
You could use a pivot table to split the data into separate columns.
You could use extra columns with formulas, as shown in Split Data Range into Multiple Chart Series without VBA.
Or you could use VBA to Split Data Range into Multiple Chart Series.
Martyn says
Jon,
This is briliant! thankyou for taking the time to work out how to do this, it has saved me about 3 days work today.
Martyn
Katrine says
Jon, thank you this is a wonderful function.
However, I have the same problem as Jason, Tuesday, November 24, 2009 at 10:55 am, where it only colors with the first value. This is because my values are text, as you mentioned, and not numerical, so the vValues = .Values does not work, but the Ubound function messes up if I try to just change the line to vValues = .XValues. I’m a beginner with vba, and I have tried, but I simply cannot figure out how to adapt this code to accept text values. Could you perhaps give me a hint?
Katrine
Jon Peltier says
Katrine –
Jason wanted to test a different range in the worksheet to apply his colors. The original code looks at
series.Valueswhich is the array of Y values. I had Jason look at
range.Valueswhich is the array of values from a worksheet range. You have mentioned
series.XValueswhich is the array of X values in the chart series.
What are the text values you want to evaluate? Certainly it’s not Y values, and I’m not sure it’s the X values. Is it a worksheet range, as in Jason’s case?
Note that UBound will break if its argument is not an array.
Ursula says
Hi Jon,
Thank you so much for this site – it’s been a huge help to me.
I’ve inherited a lot of workbooks with a lot of VBA in them, mostly to format data points in charts. The issue is that we are just now moving from Excel 2003 to 2010 and not all of this code works. It’s fine if there is only one data series per chart, and if there is no pattern, but I am increasingly needing to show 3 series per chart which necessitates the using of patterns as well as different fill colors. What I was able to do in 2003 was set the series’ default to one fill color and a black pattern foreground; while the fill color would change, the pattern remained black. Now the fill color changes, but so does the foreground color of the pattern.
The following is what I have successfully been using in 2003; can you provide some insight as to what I need to change?
Thanks so much,
Ursula
Sub FormatInitAccBHS()
Dim x As Integer
Dim val As Variant
Dim MLT As Range
Dim Tar As Variant
Dim Rxr As Range
Dim PhD As Range
Set MLT = Sheets(“Data”).Range(“C223:N223”)
Set Tar = Sheets(“Data”).Range(“X12”)
Set Rxr = Sheets(“Data”).Range(“C222:N222”)
Set PhD = Sheets(“Data”).Range(“C224:N224”)
ActiveSheet.ChartObjects(“Chart 93”).Select
With ActiveChart.SeriesCollection(“MLT”)
val = .Values
For x = 1 To .Points.Count
If val(x) >= Tar Then
.Points(x).Interior.ColorIndex = 43
ElseIf val(x) = Tar Then
.Points(x).Interior.ColorIndex = 10
ElseIf val(x) = Tar Then
.Points(x).Interior.ColorIndex = 31
ElseIf val(x) < Tar Then
.Points(x).Interior.ColorIndex = 18
End If
Next x
End With
End Sub
Ursula says
The previous post didn’t include all the code:
Sub FormatInitAccBHS()
Dim x As Integer
Dim val As Variant
Dim MLT As Range
Dim Tar As Variant
Dim Rxr As Range
Dim PhD As Range
Set MLT = Sheets(“Data”).Range(“C223:N223”)
Set Tar = Sheets(“Data”).Range(“X12”)
Set Rxr = Sheets(“Data”).Range(“C222:N222”)
Set PhD = Sheets(“Data”).Range(“C224:N224”)
ActiveSheet.ChartObjects(“Chart 93”).Select
With ActiveChart.SeriesCollection(“MLT”)
val = .Values
For x = 1 To .Points.Count
If val(x) >= Tar Then
.Points(x).Interior.ColorIndex = 43
ElseIf val(x) = Tar Then
.Points(x).Interior.ColorIndex = 10
ElseIf val(x) = Tar Then
.Points(x).Interior.ColorIndex = 31
ElseIf val(x) < Tar Then
.Points(x).Interior.ColorIndex = 18
End If
Next x
End With
End Sub
Jon Peltier says
Ursula –
I don’t know offhand what you need to change to make the code work. If you step through the code in the VB Editor (using the F8 key to advance line by line) does execution flow through the If/Else/Then block as expected? There seem to be two branches for val(x) = Tar, so only the first of these will ever be followed. Also, Tar is set to a range, so your code should compare val(x) to Tar.Value instead of Tar (thought VBA usually guesses correctly what you meant).
The bigger issue might be the use of ColorIndex, which was made obsolete in Excel 2007. The new syntax looks like this:
.Points(x).Format.Fill.ForeColor.RGB = RGB(255, 0, 255)
You’ll have to determine the desired RGB values.
Kyle says
Hello,
Could you please post a modified version of the original 2007 code that loops through all charts in a workbook. I get it to work with one chart but I have a couple hundred I frequently update.
Thanks
Jon Peltier says
Kyle –
I would first extract the working part of the routine and place it into a function
Then I would write a sub to loop through all the charts.
Miguel says
Hi, John
I’m trying to get a similar approach but specifying ranges of values. For instance:
Color in green values greater than 5
Color in red values lesser than 5
Color in grey values between -5 and 5.
How can I do that?
Thanks ;)
Jon Peltier says
Miguel –
A1 would be red and contain the value -5, A2 would be gray and contain the value 5, and cell A3 would be green and contain a very large number (like 1000). Change the address of rPatterns to “A1:A3”. Select the chart and run the code.
Miguel says
Works great, Jon
Thanks so much ;)
Andrei Tufisi says
Hi! I tried your example for 2010 and it gets all the chart in white. My conditional formatting of the numbers is greater than/ less than a value set by me. I have 18 rows, each formatted by a different value. My question is why the pie turns into white? I only have green and pink as colors. Thank you!
Jon Peltier says
Andrei –
What are the values in the pie? Are the pie slices supposed to be one color or the other?
If you step through the code, does it access each pie slice, does it determine each value properly, does it find the right range for the colors, does it find the right color for each value?
andrei tufisi says
Jon –
I have, in my case, a column from D3 to D20 with cells which are colored by certain values of the numbesr inside them. For example, i formatted each column with greater than/less than a given number set by me.
E.G. : in D3 if the number i put there is greater than 2800, the cell turns green; if not, it turns pink.
in D4 if the number i put there is greater than 3500, the cell turns green; if not, it turns pink.
I have no negative numbers or set values.
I’m a begginer with Macro and that’s why i’m asking for your help.
Thank you once again for understanding!
Kerry Birtch says
Is it possible to do a similar color coding but instead of by value, by axis name. That is to say, from the original data in this thread, can you change the color of the bar based on the values of : Comcast, Brown & Williamson, Enron, Haliburton, regardless of their value?
Jon Peltier says
Kerry –
That’s in a companion article: VBA Conditional Formatting of Charts by Category Label
Matt Herring says
How do I conditionally format by value for a pie radar chart? Each wedge of the pie is equal size, but the wedge is filled according to value. The higher value, the more the wedge is filled. I’d like for the color to be conditional to the value (higher values green, lower red)
Jon Peltier says
I’m reluctant to get into this, because using color to encode more than qualitative information is problematic. Red and green, for instance, if you’re one of the over 8% of males with color vision deficiencies. In addition, displays with radial arrangements may lead to more confusion than the designer considered possible.
What you need to do in this chart is have multiple data points for each segment, one for each color. Formulas in the source data show either a 1 for the color that should appear or 0 for colors that should not appear. The 1s appear as complete wedges, the zeros do not appear.
Asha says
Hi,
Thanks for the great explanation and the code, it worked amazingly!
I tried using the code for another project but unfortunately it didn’t work. It seems to be because the cells the chart is pulling the colours from are horizontal, rather than vertical. Therefore I just wanted to ask if there was a way to update and specify this in the code?
Thanks,
Asha
Moe says
Hi,
Will this code work for changing the colour of the marker based on value on a line chart?
If not, is there another link or what needs to be edited in the above code.
Thanks.
Jon Peltier says
Moe –
This syntax is to fill a column or bar data point:
You need to adjust it to change the marker foreground color (the outline of a marker shape) and/or background color (the fill of a marker):
Moe says
Thanks for the quick reply…However, I had no luck ( no errors either)
Maybe I’m not looking at the right article. If I may try to explain what I’m trying to do.
I have a line chart, if any of the value or either below 10 or above 40 then fill the marker with red, otherwise fill with white.
This needs to be done for about a dozen different charts, all with different upper\lower limits.
Jon Peltier says
Moe –
This is covered in Conditional Formatting of Excel Charts.
Basically you do it with an extra range of data and an extra series in the chart. Suppose your original data is in columns A and B below. You set up column C, with the min and max in C1 and C2, and the formula in C4 (which is copied down the column) is given in D4.
The chart below left is the original. I’ve added the new column of data to the chart below right, and formatted it with larger red markers and no lines. It’s dynamic, so if any of the data changes, or if you adjust the limits, a different set of points will be highlighted.
Lance says
I have what is a basic Gantt chart using a stacked bar with start date and duration and would like to update the color of the “bars” based on another column of data in the table. We call it ‘health’ and the values are 0-4 inclusive with each number representing a different color value. I don’t want to graph the 0-4 values but only want to use the value to set the back color of the other bars. I feel like I’m close (set up the table as mentioned above with the back colors that I desire) but I’m missing the connection between the chart data and looking up the value of a different, non-graphed column. Any suggestions would be appreciated.
Lin says
Hi Jon,
thank you for this usefull piece of code. Worked very well to me for the simple charts (and I found out is works just as good for the Pie charts as well).
But I also need stacked charts which can take the colors from the label value, (2 or more data columns) is this possible as well? So I have 5 countries with a reference colour, and then charts which contain per year data for 3 countries. This will result in a chart with a stacked column per year, showing the data of the different countries in a pile. (I wish I could upload an image to make it more clear).
Thank you in advance