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.
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 chart with several series, and the series may be in any order. However, you want to color a category according to its name, so that Halliburton always is colored red and Enron is always blue.
The following protocol allows you to color the series in a chart according to colors you’ve reserved for each series. The range below illustrates the data: range A1:A4 contains a list of the series names, with each cell filled with the desired color for that series’ data in a chart. A6:E10 contains data for Chart 1, and A12:E16 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 seriess, based on their names.
Excel 2003 and earlier:
Sub ColorBySeriesName()
Dim rPatterns As Range
Dim iSeries As Long
Dim rSeries As Range
Set rPatterns = ActiveSheet.Range("A1:A4")
With ActiveChart
For iSeries = 1 To .SeriesCollection.Count
Set rSeries = rPatterns.Find(What:=.SeriesCollection(iSeries).Name, _
LookAt:=xlWhole)
If Not rSeries Is Nothing Then
.SeriesCollection(iSeries).Interior.ColorIndex = _
rSeries.Interior.ColorIndex
End If
Next
End With
End Sub
Excel 2007 and later:
Sub ColorBySeriesName()
Dim rPatterns As Range
Dim iSeries As Long
Dim rSeries As Range
Set rPatterns = ActiveSheet.Range("A1:A4")
With ActiveChart
For iSeries = 1 To .SeriesCollection.Count
Set rSeries = rPatterns.Find(What:=.SeriesCollection(iSeries).Name, _
LookAt:=xlWhole)
If Not rSeries Is Nothing Then
.SeriesCollection(iSeries).Format.Fill.ForeColor.RGB = _
rSeries.Interior.Color
End If
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 series names and the colored table in A1:A4.
Related Articles about VBA Conditional Formatting of Charts
- VBA Conditional Formatting of Charts by Value
- 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
Tony says
Jon – I’m not a huge fan of the color choices, but it’s a nice trick that is pretty straightforward. Nice work!
Jon Peltier says
Tony – Thanks for the comment. The colors are the bright versions, intended for lines and markers, rather than the lighter versions intended for fills like these. It’s a snap to update the colors since I still have the VBA procedures in the workbook, but the thought of uploading a dozen or more pictures….
The trick is pretty easy, because you can much more easily change a cell color than figure out which color index to plug into the code. I can even trust my users to do that part right.
I don’t know what’s up with that code box; it keeps forgetting the indentation settings.
John Mansfield says
Jon,
Each of your three recent entries on chart formatting techniques are very practical. I can see using the macros at work to insure that the chart colors remain uniform across different documents.
On another note, to keep the indentation settings for the VBA code, maybe the “pre” tag would work . .
Your code here
You can format it in your CSS template similar to any other tag . .
pre {
padding: 5px;
border: 1px solid #000000;
background-color: #FBFAF5;
font-family: verdana, georgia, times new roman, times, serif;
font-size: 11px;
color: #000000;
}
The downside is that it may not pick up the color changes within the code.
— Just thought I’d suggest it.
John Mansfield says
Jon,
Sorry, it looks like I didn’t pick up the “pre” tag. Hopefully this works . .
< pre > Your code here < / pre >
Jon Peltier says
John –
Thanks for the suggestion about the pre tags. I’ve actually used pre tags about since I started my first web site in 2001. Unfortunately the WodPress editor takes liberties with the code you enter, and sometimes steps on tags, especially pre and br. Grr. The code lister I use is the one Dick K uses on Daily Dose of Excel, and it seems okay, but sometimes the indents go away, and I need to figure out what to do about the garish colors.
Davie says
Jon, I am using your code to format a graph with the option of selecting 19 colours.
It worked successfully earlier today but now has an error message of “Object Variable or With block variable not set”.
I cannot figure out what is causing this. Has anyone came across this problem before?
The code is exactly what I require.
Here is the code I used.
[vb]Sub ColorBySeriesName()
Dim rPatterns As Range
Dim iSeries As Long
Dim rSeries As Range
Set rPatterns = Sheets(“Tools”).Range(“A1:A19”)
With ActiveChart
For iSeries = 1 To .SeriesCollection.Count
Set rSeries = rPatterns.Find(What:=.SeriesCollection(iSeries).Name)
.SeriesCollection(iSeries).Interior.ColorIndex = rSeries.Interior.ColorIndex
Next
End With
End Sub[/vb]
Regards,
Davie
Jon Peltier says
Davie – You didn’t indicate which line produced the error. The error you cited could occur if the active workbook has no sheet names “Tools”, and also if there’s no active workbook at all, or if a chart isn’t selected when the code first runs, or if there is no SeriesCollection(iSeries).
Also note that you can get formatted code in your comment by enclosing the code in VB tags, like these without spaces between [ and ]:[ v b ]
Your code here
[ / v b ]
I’ve applied VB tags to the code in your comment above.
These [ v b ] and [ / v b ] tags no longer work.
Davie says
Jon, the error occurs on line 10. The active workbook has a sheet “Tools”, I have selected the chart as the ActiveChart before I run the macro.
I want ultimately to run this in the formatting table macro.
Davie
Jon Peltier says
Davie – What kind of chart is it? Line 10 is attempting to change
.SeriesCollection(iSeries).Interior.ColorIndex
but some kinds of series have no Interior (line or XY charts, for example), so this line will fail. You would have to modify the code to adjust .MarkerForegroundColorIndex, .MarkerBackgroundColorIndex, and .Border.ColorIndex for such a series.
Davie says
Jon, it is a stacked column graph. I tried “MarkerBackgroundColorIndex but still got the same error message.
Davie
Davie says
Jon, i recreated the graph and it is working ok. Don’t know what the problem was. Thanks for your help.
Davie
tom says
hi Jon
is it possible to apply this to cells that are conditionally formatted
the color of my series are determined by other financial information therefore i use conditional formatting to assign colors
i have had no luck so far
thanks
tom
Jon Peltier says
Tom – VBA only knows the base formatting for a cell, not what is applied by conditional formatting. You would have to write your formatting conditions into the code that selects the colors for the chart.
Drandon Campbell says
Jon, as usual your excel knowledge has helped me tremendously in the past. I thank you very much. I get the error “Object Variable or With block variable not set” on the line “.SeriesCollection(iSeries).Interior.ColorIndex = rSeries.Interior.ColorIndex”. I am using a Bubble Chart. Anyway I can do this same color principal on this type of chart?
Thank you.
Jon Peltier says
Drandon –
I just tested it, and the code as written works fine in a bubble chart. The only way I replicated your error in the indicated line is if the series name isn’t listed in the range where the colors are defined. Check for a typo or that your VBA range includes all the formatted cells. In other words, this fails:
Set rPatterns = ActiveSheet.Range(“A1:A4”)
if you have more than four series.
Drandon Campbell says
I have 10 Payers (Top 10 – will not change)
Here is my code –
Sub setColor()
Dim rPatterns As Range
Dim iSeries As Long
Dim rSeries As Range
Set rPatterns = Sheets(“LOOKUPS”).Range(“K51:K60”)
ActiveSheet.ChartObjects(“Chart 18”).Activate
With ActiveChart
For iSeries = 1 To .SeriesCollection.Count
Set rSeries = rPatterns.Find(What:=.SeriesCollection(iSeries).Name)
.SeriesCollection(iSeries).Interior.ColorIndex = rSeries.Interior.ColorIndex
Next
End With
End Sub
Thanks Jon.
Jon Peltier says
That’s the code. Things to check:
+ Is the correct chart being activated?
+ Is K51:K60 the correct format lookup range?
+ Does the chart have the right number of series?
+ Are the series names exactly as found in the lookup list (no unintentional spaces, etc.)?
+ Does the code fail on the first loop, or do some series get the new formatting applied?
Drandon Campbell says
There was actually two more series than I thought (two are set error bars- vertical and horizontal). I reset K60 to K62 and set the last two cells to no color. Still didn’t work – same error. I think my issue may be that my Payers actually constitute a total of 50 or so. K51:K62 are referencing the chart data (=’Bubble Graph’!B16 and so on). When I change the year the top 10 payers change. I don’t think I have enough colors for all 50 Payers :)
Oh well. Thanks a lot though.
Jon Peltier says
If a series name isn’t found, Excel can’t find a cell to select a color from. You need to trap for this error:
If Not rSeries Is Nothing Then .SeriesCollection(iSeries).Interior.ColorIndex = rSeries.Interior.ColorIndex End Ifariella says
Jon,
I copied your example and it worked no problem. But, when i try to apply it to my sheet i get the same error as others above. Does it not work if the color reference cells are formulas, not values?
Jon Peltier says
Ariella –
There are several reasons that the code may fail. It helps me to debug if you can indicate the error number and description and the line of code which is highlighted when the error occurs.
I have added the lines of code from a couple comments above, which bypasses the formatting line if the series name is absent from the lookup list.
If the lookup list is generated by formulas, you have to make sure that the formulas produce the series names exactly. A stray space character or a character with mismatched capitalization will prevent matches.
ckz says
Can the above code be used for pie charts?
I have a worksheet where data is entered. This data is copied to a hidden worksheet where the chart creates its datarange.
On the page the user enters the data, I have a button for the user to view the charts.
When the comandbutton is clicked – I tell the program to hide any rows with values of zeroes in the hidden worksheet so that the only information that is charted has a value.
I want to add code to assign the correct colors to each category label.
Here is my code… I am far from a programmer, and know little about the correct language to use for VBA.
Private Sub CommandButton3_Click()
Dim rng As Range
For Each rng In Worksheets(“ChartData”).Range(“VolumeRange”)
If rng.Value = 0 Then
Worksheets(“ChartData”).Rows(rng.Row & “:” & rng.Row).Hidden = True
End If
Sub ColorBySeriesName()
Dim rPatterns As Range
Dim iSeries As Long
Dim rSeries As Range
Set rPatterns = ActiveSheet.Range(“A1:A14”)
With ActiveChart
For iSeries = 1 To .SeriesCollection.Count
Set rSeries = rPatterns.Find(What:=.SeriesCollection(iSeries).Name)
If Not rSeries Is Nothing Then
.SeriesCollection(iSeries).Interior.ColorIndex = _
rSeries.Interior.ColorIndex
End If
Next
End With
End Sub
Next
Application.ScreenUpdating = False
Sheets(“Water Table”).Visible = False
Sheets(“Pie Charts”).Visible = True
Sheets(“Pie Charts”).Select
End Sub
Jon Peltier says
ckz –
The technique works just fine for a pie chart. But not this particular implementation. A pie chart has but a single series, plus a variety of categories. Formatting by series name will not help, but formatting by category should do what you want. See my related post VBA Conditional Formatting of Charts by Category Label.
However, you can’t just drop a sub into the middle of another sub and expect it to work. Put the sub from this site into a regular code module, and call it from byt event procedure (CommandButton3_Click) by simply typing its name in a line of code.
Then make sure you reference the appropriate sheet and range for the color lookup range and the appropriate chart.
mikael says
I have already created an excel tab as a template for a report, with severa charts, and I have a macro that will replicate the pages.
Now I want to write a macro to replicate the entire page, including charts.
Is there a way I can view the proerties of a chart already formated, so that I can copy the properties into a vba module to replicate the chart, without having to recreate the chart while recording a macro in order to capture the coding of the formatting that I am applying the chart being recorded?
Jon Peltier says
Mikael –
Make a template, that is a worksheet or workbook with all the ranges and charts the way they need to be for your report. Then simply paste the new data values into a copy of the template. The ranges (all the formulas) will update, and so will the charts.
Save the workbook as a separate Excel template (XLT file), then with the program, insert a new sheet based on the template. Step two is pasting in the data. There need not be a step three.
Trying to do this property by property in a chart is a daunting task. There are so many properties that must be considered, some properties exist for only certain chart types, or for certain axis types.
john says
I am trying your code for a number of line chart where I want the colors to be the same for each line series in Excel 2003. Your script worked for an area chart, but I needed to modify it for a line chart.
I tried to modify your code, but get an error at .SeriesCollection(iSeries).Border.ColorIndex = rSeries.Border.ColorIndex
the error is “The object doesn’t support this property or method. ”
Thanks for your help
Sub ColorBySeriesName()
Dim rPatterns As Range
Dim iSeries As Long
Dim rSeries As Range
Set rPatterns = ActiveSheet.Range(“A1:H1”)
With ActiveChart
For iSeries = 1 To .SeriesCollection.Count
Set rSeries = rPatterns.Find(What:=.SeriesCollection(iSeries).Name)
If Not rSeries Is Nothing Then
.SeriesCollection(iSeries).Border.ColorIndex = rSeries.Border.ColorIndex
End If
Next
End With
End Sub
Jon Peltier says
John –
Two problems with using rSeries.Border.ColorIndex
1. A range has a Borders property, not a Border property.
2. If a cell has more than one color among its borders, rSeries.Borders.ColorIndex returns Null, because there is no single color index that applies. You could use rSeries.Borders(xlEdgeTop).ColorIndex, but that’s a bit hard to work with. Also, it’s harder for a user to set border colors than interior colors. I would recommend you stick with the interior color and rSeries.Interior.ColorIndex
john says
Previously, you mentioned that the procedure won’t work with a line chart. What procedure would you write to change a line chart.
Davie – What kind of chart is it? Line 10 is attempting to change
.SeriesCollection(iSeries).Interior.ColorIndex
but some kinds of series have no Interior (line or XY charts, for example), so this line will fail. You would have to modify the code to adjust .MarkerForegroundColorIndex, .MarkerBackgroundColorIndex, and .Border.ColorIndex for such a series.
Jon Peltier says
John –
Here is what I would use to color the lines and markers in a line chart:
Sub ColorBySeriesName() Dim rPatterns As Range Dim iSeries As Long Dim rSeries As Range Dim iColorIndex As Long Set rPatterns = ActiveSheet.Range("A1:H1") With ActiveChart For iSeries = 1 To .SeriesCollection.Count Set rSeries = rPatterns.Find(What:=.SeriesCollection(iSeries).Name) If Not rSeries Is Nothing Then iColorIndex = rSeries.Interior.ColorIndex With .SeriesCollection(iSeries) .Border.ColorIndex = iColorIndex .MarkerForegroundColorIndex = iColorIndex .MarkerBackgroundColorIndex = iColorIndex End With End If Next End With End Subjohn says
Thanks for your help with this.
I am getting an error at the line “iColorIndex = rSeries.Border.ColorIndex”. “Object does not support property or method.”
Jon Peltier says
Doh! That should be
iColorIndex = rSeries.Interior.ColorIndex
john says
Thanks for your help.
john says
Another query, I would like the markerstyle to vary between the series on a particular chart like the color varies, but be constant across charts for each series.
Jon Peltier says
You need to include the code to manipulatee the marker style:
Sub ColorAndMarkerBySeriesName() Dim rPatterns As Range Dim iSeries As Long Dim rSeries As Range Dim iColorIndex As Long Set rPatterns = ActiveSheet.Range("A1:A4") With ActiveChart For iSeries = 1 To .SeriesCollection.Count Set rSeries = rPatterns.Find(What:=.SeriesCollection(iSeries).Name) If Not rSeries Is Nothing Then iColorIndex = rSeries.Interior.ColorIndex With .SeriesCollection(iSeries) .Border.ColorIndex = iColorIndex .MarkerForegroundColorIndex = iColorIndex .MarkerBackgroundColorIndex = iColorIndex End With Select Case LCase$(rSeries.Offset(, 1).Value) Case "square" .SeriesCollection(iSeries).MarkerStyle = xlMarkerStyleSquare Case "circle" .SeriesCollection(iSeries).MarkerStyle = xlMarkerStyleCircle Case "triangle" .SeriesCollection(iSeries).MarkerStyle = xlMarkerStyleTriangle Case "diamond" .SeriesCollection(iSeries).MarkerStyle = xlMarkerStyleDiamond End Select End If Next End With End SubSteven says
Hi Jon,
I’m using this code in a worksheet and it works fine except for one thing: When a field in the designated row with colors consists of only one character, it does not recognise that color in the chart. I tested this by adding an extra character and all’s fine. But as you may guess, I really need it to consist of just one character.
Any thoughts on this one? Help is much appreciated!
Thanks,
Steven.
Sub ColorBySeriesName()
Dim rPatterns As Range
Dim iSeries As Long
Dim rSeries As Range
Set rPatterns = ActiveWorkbook.Worksheets(“Schema Aangepast Ursulaklooster”).Range(“A2:A49”)
With ActiveChart
For iSeries = 1 To .SeriesCollection.Count
Set rSeries = rPatterns.Find(What:=.SeriesCollection(iSeries).Name)
If Not rSeries Is Nothing Then
.SeriesCollection(iSeries).Interior.ColorIndex = _
rSeries.Interior.ColorIndex
End If
Next
End With
End Sub
Jon Peltier says
Steven –
I’ve changed Halliburton to H and Enron to E, in both the chart source data range and in the range that defines the colors. The code updates the chart properly.
steven says
Weird, there must be something wrong with my worksheet. I found out in the meantime that the letter ‘r’ DOES work, but the letters p/w/m/f/g do not. I did not check other letters though and these are the only single character fields in the datasheet. I’ve checked for loose spaces or any inconsistencies between the sourcefields and the chart-data but found nothing out of the ordinary.
I’m using excel 2000 and defined a lot of my own colors, but this can hardly be the cause. Besides, when I add any character to these fields, the code works perfectly. The only similarity between the dead records i can find is the fact that they’re only one character long. But since the ‘r’ does work, I’m rather at a loss as to what the problem could be. Perhaps it’s best to start from scratch. I’ll let you know if that approach works.
BTW the chart data is imported through a pivot table from MS access. Could this be at the heart of the problem? Keeping in mind that the code does work for 43 out of 48 records and there’s no inconsistencies between the fields…
Many thanks for your quick replies!!!
Jon Peltier says
Steven –
Sounds like gremlins in the worksheet. What if you use a single character plus a space, like “p “? Do you have similar sheets that don’t have this problem? I hate to have to suggest it, but it might be worthwhile to recreate the worksheet from scratch.
Steven says
Well, this thing is keeping me awake at night. I’m sorry I keep bothering you like this, but here’s something weird you might find interesting:
If you create the following cells A1-A6 in this order: e/ei/hi/h/ri/r and add some colors and make that the color-range. Then make a sheet with two series and give them some values, with rownames corresponding to the colorrange, but not necessarily in that order (like your example on the top of the page). Now if you make a chart based upon that data and run the code, the result is that the rows that start with the same letter get the same color (at least in my 2000 version).
If you change the sorting of the color-range to ei/e/h/hi/r/ri and run the code on the same chart again, then the colors are projected as they should be. If you add any other character (or space) everythings allright no matter the order of the data. I’ve tried this several times in new sheets, workbooks and with different data and I get this result all the time.
So it looks like a consistant error, but there’s something weird. Using the data above, in the first two cells the single character comes before the double character cell, but in the subsequent cells this is the other way around. This is simillar to the odd ‘r’ in my previous post. This seems to work only for the first two cells, for if you change the order of the last two (ri/r) they get the same color again. If you mess about with it some more there are more inconsistancies for which I have no explanation (and no wish to type here :) ).
I wonder if you can recreate this phenomenon and hope you have another smart solution to this problem.
Many thanks for the time you spent on my questions!
Jon Peltier says
Steven –
Ah, what we need to do (and I should have done) was to specify that Excel should search for cells whose whole content matches the search string:
Set rSeries = rPatterns.Find(What:=.SeriesCollection(iSeries).Name, LookAt:=xlWhole)
I’ll change the post.
Steven says
Excellent!!! Works like a charm!
I’m gonna buy you a cup of coffee!!!
:D
THANKS!!
paresh says
Hi Jon,
Was going through your articles on parallel coordinates. In that article you mentioned that you formatted all the lines to light grey. Did you format this line by line or is there a way to format all lines at one time – Would like to avoid VBA if possible.
paresh
Jon Peltier says
Hi Paresh –
You can’t format the lines all at once. You can format one, then select the next, and press the F4 shortcut key (“Repeat Last Action”). This works nicely in 2003, repeating all changes from the trip to the formatting dialog. In 2007 it repeats at most the last one action (like line color or marker type), and sometimes none at all.
paresh says
Hi Jon,
Thanks.
But a parallel coordinate chart would have a large number of line and changing it one by one using F4 key would be tedious. Maybe the only alternative [ apart from using VBA ] is to use the first option in chart styles which give monchromatic graphs. Another alternative I have tried is to create chart template where say five lines have the same color and tried to use it for the new chart – works but only for five series for the sixth the color comes again.
paresh
paresh says
Hi Jon
Continued my research on the subject and this is what I discovered;
a. maximum number of series in a chart is 255. This will seriously limit the use of parallel coordinates in excel as one would expect analysing hundreds of items at one time.
b. An easy way to create a line chart with one color is ;
1. create a line chart with one series
2. modify the line as per requirements; color, thickness.
3. Copy the series in the chart using control c
4. paste it as many times as you want using contol v [ the 255 limit will apply]
5. save the chart as template
In the future whenever you draw a chart you can change the chart type to the saved template — you will have a line chart with one color.
If the limit was not there, one could have visualised the use of parallel coordinates in excel with incredible scope using the filter functionality.
Anyway time for an article on phd on chart templates.
Jon Peltier says
The F4 approach isn’t too tedious. Start with series 1, format it, click the up arrow to select series 2, click F4, repeat up arrow and F4 as required.
If you want to use a template, you need to format as many series as you will need. not just five.
Jon Peltier says
Paresh – How do you accomplish steps 3 and 4? You can’t select a series and copy it.
Jon Peltier says
Follow up.
Paresh has sent me a chart offline which he constructed by formatting one series, then selecting it, copying it using Ctrl+C, and pasting it using Ctrl+V. This functionality is new in Excel 2007, and is applicable to more than just one series. It seems if you copy any other chart element besides a single series, pasting duplicates all series in the chart.
Similar behavior exists in 2003: copy the chart area of a chart, select the same or a different chart, and paste to adds the data of the copied chart to the active chart. I had never noticed this in 2007: if it’s a feature it’s a useful one. If it’s a bug, it’s still a useful one.
Joel Lahrman says
Jon, just found the code you listed above and it has been a TREMENDOUS help, thanks.
I’m looking through the conversation and I don’t believe the following question has been asked…
In addition to changing the colors of the interiors of the bars in my chart, I’d like it to be able to change the color of the label font as well. I’m putting the labels at the base of the bars. One of my bars is set to show up black, so obviously a label with black font won’t show. I’m tried variations in my code to get it to pick up the font color in addition to the background color in my rPatterns range, but so far no luck.
Any ideas on this? Thanks much, I’m far from a programmer myself but am pretty good at adapting code.
Jon Peltier says
Joel –
Color the font in the cells the color you want the label text to be. Then right after this line:
.SeriesCollection(iSeries).Interior.ColorIndex = _ rSeries.Interior.ColorIndexinsert this:
.SeriesCollection(iSeries).DataLabels.Font.ColorIndex = _ rSeries.Font.ColorIndexJoel Lahrman says
Thanks so much again!
Stacey says
I was so happy to discover this code today and can see it being very useful. However, when I run a macro (ColorBySeriesName or ColorByValue) the colors that get implemented don’t always match the colors I’ve applied to my reference cells (A1:A4). Sometimes the colors are close, other times they are way off (completely different hue, or more often it seems a shade of gray). This occurs whether I use colors selected from theme or from “more colors.” I did create two custom color themes today, but I don’t see how that could affect the ColorBy macros because isn’t the vba taking the ColorIndex from the reference cells and applying that directly to the chart features?
Jon Peltier says
Stacey –
Themes, eh? Sounds like 2007. I have yet to come to grips with the 2007 color system, and with the formatting commands for adding color to chart elements in 2007. Probably it would work better if the code is adjusted to measure the RGB of the filled cell instead of its colorindex or theme identities, then apply this RGB to the chart element.
Some day I’ll take a crack at it.
Sri says
I am new to Excel VBA Programming. I have to write a VBA code which would copy the complete Chart format and style and then Replicate in other Worksheets and workbooks the same Format and style, but with different Data Source. (The Chart is defined by user manually, and i have to get the settings of this Chart).
If anyone knows please help me in completing this task!!!
‘Thanks in Advance!!!
Jon Peltier says
Sri –
It would probably be easier to copy the chart, paste it into the new sheet, and series-by-series reassign the data.
Sri says
Hey Jon,
Thanks for the fast reply!!!
Currently i am doing this work manually Chart by Chart… and it takes a lot of time.
I was looking for automating this because i have almost 60 to 70 Charts in each workbook. Like this i have several workbooks to change. Currently i am working in reporting department of a company.
Jon Peltier says
Sri –
If the chart isn’t too complicated, select the chart area of the chart with the formatting you want, copy it, select the chart that you need to format, and use Paste Special > Formats.
Karyn Orts says
yeah,I just thought you may possibly need to know that your blog is messed up when you view it on my iphone. I?m not positive if it has something to try and do with my telephone?s browser or your website? just saying…
Jon Peltier says
Hi Karyn –
I’ve made no effort to make a mobile version of my blog. That said, my Droid displays my website with a minimum of distortion. It’s probably an incompatibility with the iPhone browser.
Boris Parizot says
I was trying to find such function in Excel for a real while and could never achieve it ! Thanks !!
However, I had the 2007 problem that the color were not properly picked-up.
Also, even though I understand almost nothing to what I am doing, I found a crosoft page describing the iColorIndex function, where they actually say you can use iColor instead, what I did and works perfectly…
Thank you again for the code !
Alexis says
This code is exactly what I need but I can’t get it to work. I have a chart with a list of categories that I’d like to code. The categories are in sheet “seriescolors” and each cell has been colored with the color I’d like the series. The chart is in a sheet called “exhibit”.
Dim rPatterns As Range
Dim iSeries As Long
Dim rSeries As Range
‘ Set location of category names and their colors
Set rPatterns = ActiveWorkbook.Worksheets(“seriescolors”).Range(“A1:A19”)
/*Then there’s code that generates the chart*/
With ActiveChart
For iSeries = 1 To .SeriesCollection.Count
Set rSeries = rPatterns.Find(What:=.SeriesCollection(iSeries).Name, LookAt:=xlWhole)
.SeriesCollection(iSeries).Interior.ColorIndex = rSeries.Interior.ColorIndex
Next
If Not rSeries Is Nothing Then
.SeriesCollection(iSeries).Interior.ColorIndex = rSeries.Interior.ColorIndex
End If
End With
It gives me an error at “.SeriesCollection(iSeries).Interior – “Object Variable or With Block Not Set” and the “iSeries” is coming up as 1. Any ideas what might be wrong here? Let me know if I can give more detail. Thanks!!!
Jon Peltier says
You’ve misplaced the If/Then statement
Leah says
Jon,
I can’t thank you enough for the help on your site. I have successfully built the Gantt Chart for Repeated Tasks and now need the bar for each task to be a different color based on selections set by user. (In other words, I do not want to individually format each bar)
I’ve attempted the VBA format by Series Name as well as by Category Label and haven’t gotten anywhere. I was thinking it may be due to the layers of different series in the Gantt chart? Anyhow, here is the current code I’m working with (one Sheet named DATA and one Chart named IA IT PROJECTS):
Sub ColorBySeriesName()
Dim rPatterns As Range
Dim iSeries As Long
Dim rSeries As Range
Set rPatterns = Sheets(“DATA”).Range(“C19:C25”)
ActiveSheet.ChartObjects(“IA IT PROJECTS”).Activate
With ActiveChart
For iSeries = 1 To .SeriesCollection.Count
Set rSeries = rPatterns.Find(What:=.SeriesCollection(iSeries).Name, LookAt:=xlWhole)
If Not rSeries Is Nothing Then
.SeriesCollection(iSeries).Interior.ColorIndex = rSeries.Interior.ColorIndex
End If
Next
End With
End Sub
I had first color coded the 7 task names (AKA Ambulance 1, 2, 4, 7 from your charting tutorial) but thought maybe I should color code the associated number – neither worked.
Currently, this is my error: “Run-time error ‘5’: Invalid procedure call or argument”
As you can probably guess, this is my first experience with VBA.
Thank you in advance.
Sincerely,
Leah
Jon Peltier says
Leah –
“Neither worked”… The code ran for me, though it didn’t match the colors exactly. Do the labels in the patterns range exactly match the series names (spelling and capitalization)?
Excel 2007+ uses different chart formatting syntax than I wrote about.
Try this variation:
richie says
hi jon,
i am making a gnatt chart over a 24 hr period, i am tring to colour the bars based on a word i put in a cell, i.e peoples names, and what they are doing and for how long
i.e cleaning
waiting
off these are in coloum e. i have put the cells onto the worksheet and tried using your example above changing the range to where i have put the coloured cells and the jobs, however i cant work why it wont change the bars to the colours.
any help would be grateful
richie
Patrick says
Hi Jon,
Thank you for all of this wonderful information. You have quite the gift.
I was trying to apply the 2007-color version to all charts and sheets in the workbook (not just the active sheet). Being a VBA novice, I took a (working) variant of your macro:
and tried to change it this way:
The error I get is “Compile Error: Invalid or unqualified reference,” and the VBA editor highlights .SeriesCollection in the “For SourceRangeColor” line.
If you’re not too busy, I would love to learn what I’m doing wrong, and how to fix it.
Thank you again,
Pat
Jon Peltier says
The immediate problem is that .SeriesCollection isn’t defined in the code following “For Each MySeries…”. So before “For SourceRangeColor” insert
With oChart.Chartand after the associated “Next” insert
End WithYou’ll find in general that using descriptive variable names, like iSeries, is better than a confusing variable name like SourceRangeColor. The variable is looping series, not colors or ranges.
Patrick says
Jon,
Thank you for such a prompt reply! I applied your suggestions (including changing the offending variable names) and made one minor change (replaced activesheet with sht).
Here is the edited macro for posterity’s sake. Thank you once again!
Pat
Jon Peltier says
Pat –
Thanks for sharing your updated code.
James says
Hi Jon,
Thanks for this, I have found it very useful.
I am trying to use the code below (provided by yourself) to colour a set of 100% stacked bar chart on the active worksheet. My colours are in the range A2:A8.
The problem is that when I run the macro it works, but only for the first series of data. What do I need to change to get it to colour the second and third series of data?
Thanks for your help
CODE:
Sub ColorByCategoryLabel()
Dim rPatterns As Range
Dim iCategory As Long
Dim vCategories As Variant
Dim rCategory As Range
Dim chtob As ChartObject
Set rPatterns = ActiveSheet.Range(“A2:A8”)
For Each chtob In ActiveSheet.ChartObjects
With chtob.Chart.SeriesCollection(1)
vCategories = .XValues
For iCategory = 1 To UBound(vCategories)
Set rCategory = rPatterns.Find(What:=vCategories(iCategory))
.Points(iCategory).Interior.ColorIndex = rCategory.Interior.ColorIndex
Next
End With
Next
End Sub
Jon Peltier says
James –
You need to loop through all the series:
James says
Jon,
That makes sense, thank you.
I’ve just tried to use the macro, however it still appears to just colour the first series of data. Does the data table layout need a particular format?
Thanks for your help
Jon Peltier says
James –
Duh. I included a loop variable, but still check only series 1, because I didn’t change .SeriesCollection(1) to .SeriesCollection(iSrs). It’s now fixed in the code above.
James says
Hi John,
Now works a chart, thanks.
Would it be possible to colours the stacked bars with different colours? At the moment, the colours are the same for each stack/part within the respective bar.
Can you help?
Thanks
James says
Hi Jon,
I wondered if you’d had any success or hints on how to create different colours within the stacked bar chart?
Thanks
Jon Peltier says
James –
To see how to separately format individual points within a series, check out the two links at the end of the article.
Patrick says
Jon,
I’m back again, this time with a slightly different request. Rather than change the color by series, is it possible to change the color by items in a series? For example, if I wanted to create a pie chart with 4 items and their percentages (where all 4 items belonged in one series), and I wanted the four items to come out in the color specified in a master list (akin to the “color_scheme” of the example below), what would I do?
This wonderful code you gave me works for multiple series, but not for items within a series. Any help you could provide would be greatly appreciated.
Sub ColorBySeriesName()
Dim sht As Worksheet
Dim oChart As ChartObject
Dim MySeries As Series
Dim rPatterns As Range
Dim iSeries As Long
Dim rSeries As Range
Dim MyChart As Chart
For Each sht In ActiveWorkbook.Worksheets
For Each oChart In sht.ChartObjects
For Each MySeries In oChart.Chart.SeriesCollection
Set rPatterns = Sheets(“Color_Scheme”).Range(“A1:A18”)
With oChart.Chart
For iSeries = 1 To .SeriesCollection.Count
Set rSeries = rPatterns.Find(What:=.SeriesCollection(iSeries).Name, _
LookAt:=xlWhole)
If Not rSeries Is Nothing Then
.SeriesCollection(iSeries).Format.Fill.ForeColor.RGB = _
rSeries.Interior.Color
.SeriesCollection(iSeries).Format.Line.ForeColor.RGB = _
rSeries.Interior.Color
End If
Next
End With
Next MySeries
Next oChart
Next sht
End Sub
Here is the code I tried (it failed):
Sub ColorBySeriesName2()
Dim oChart As ChartObject
Dim myseries As Series
Dim rPatterns As Range
Dim Listvalues As Variant
Dim rSeries As Range
Dim MyChart As Chart
Dim iseries As Long
Dim i As Integer
For Each oChart In ActiveSheet.ChartObjects
For Each myseries In oChart.Chart.SeriesCollection
Set rPatterns = Sheets(“sheet1”).Range(“A1:A5”)
With oChart.Chart
Listvalues = myseries.Values
For iseries = 1 To .SeriesCollection.Count
For i = 1 To UBound(Listvalues)
Set rSeries = rPatterns.Find(what:=.SeriesCollection(iseries).Name, lookat:=xlWhole)
If Not rSeries Is Nothing Then
myseries.Points(i).Interior.Color = rSeries.Interior.Color
End If
Next
Next
End With
Next myseries
Next oChart
End Sub
Thank you!
Patrick
Jon Peltier says
Patrick –
By “items in a series”, do you mean VBA Conditional Formatting of Charts by Category Label?
Patrick says
Jon,
I did mean category label, and that looks like exactly what I needed.
Thanks again!
Patrick
Danny V says
Hello Jon,
This thread is a lttle old – but it helped me out tremendously today. I am a beginner in the macro arena, I think this was the first successful one I have used. I have a question – I am using this because I have a series of charts in a worksheet, and wanted the chart lines to be the same color from chart to chart depending on the series name from chart to chart. The above macro worked perfect for that. My question – my charts also all change based on a users selection from a drop down list (the user can select from 15 regions in a drop down list to see data specific to their region), is there a way to make the charts automatically update to the color scheme I have defined? Right now, I have to run the macro each time a new selection is made on the drop down list. My users of this sheet will likely not have a clue how to run a macro (I barely do myself), so I wanted to see if I could have this automatically run when the file is opened, and every time a new selection is picked from the drop down list.
Any advice is appreciated! Thank you!
Danny
Wes says
Hi Jon,
You are a God sent! Just thought I’d start with that.
I know this thread is a couple years old but I could really use your help! So I have 0 VBA coding experience, told my boss that in the interview and was told I wouldn’t need such skills, well here I am today being asked to complete a task that I researched and can only be completed by VBA and when I ask for help the reply was “we are counting on you.” Well with that being said after countless hours of research I found this site and a solution to my troubles but I need a couple modifications to the code to make it nothing less then perfect. Please bare with me as I copy and paste a lot of codes and ask you if you can, or if it is possible to combine them to get my task done. So on this thread I found the code that seems it will work best for me. What I am looking for is a code that will have the colors on a line graph, this includes the color of the line the color of the marker fill, the marker style and the data label style, move with the brand if the brand moves. So on my spreadsheet the graph moves with the brand so for instance if Company A is number 1 this week but next week drops to spaces and moves to number 3 a new company never on the graph comes in and claims the number one spot I need all the color coding for company A to move with it and the new company to have the color I assign it. I really hope that makes sense, with that being said below you will see the code I found on this thread that works somewhat:
In this code I need to include something you said to a participant earlier about changing the font color of the cells font to apply it to the data labels but I cannot find where I need to insert this line:
Also I found this on another code during my hours of research and wish to implement it if possible:
I believe this will allow me to run the macro and it will run for every chart on the Worksheet instead of me having to click every chart and run the macro. Please correct me if I am wrong! I do not oppose running it for every chart since you can assign a short cut key but I know this is something my boss is going to ask.
For the marker options I see inside the code that you have wrote lines to assign marker options but is it possible to apply specific marker options to specific brands? For instance Company A is always Diamonds Company B is always a square so on and so forth?
I know this is a lot I just threw at you on an old thread so I am unsure if you will even see this but if you do and you have the time to supply me and answer, well then you will help me earn some real cool points with my boss, until they ask me to do it again that is!
Thank you for you Blog Jon
Jon Peltier says
Wes –
I think this has what you need. Not tested…
Wes says
Hi Jon,
Here is an update I wrote this line of code
.DataLabels.Font.ColorIndex = rSeries.Font.ColorIndex
Now I am trying find a way to bold the data label, slowly but surely.
Wes says
Jon,
Update 2.
looks like I got the data labels to be bold, now if I figure out the Marker Styles I will be set! Below is my code so far (well your code with 2 lines I just so happened to come up with)
Jon Peltier says
If all of the labels are going to be bold, all you need is:
.DataLabels.Font.Bold = True
The markers are defined by a label in the cell to the right of the cell with the matching color, where the label is “square”, “circle”, etc.
Wes says
Hi Jon,
I never knew you responded because I didn’t get an email update, not sure if I was supposed to or not though I just kind of assumed.
But anyway It Works! Thank you Jon I really appreciate your help. As challenging as it was think I may take some steps at learning VBA in the near future.
Thanks again,
Wes
Wes says
Jon,
It me again unfortunately. It worked perfect in my test document now that I am trying to take it live to my actual file every thing is working but the data label codes. I am getting an error message that reads
“Run-time error ‘-214746259 (80004005):
Unable to get the Count property of the DataLabels class.”
here are the codes that are highlighted
.DataLabels.Font.ColorIndex = rSeries.Font.ColorIndex
.DataLabels.Font.Bold = rSeries.Font.Bold
Even the .DataLabels.Font.Bold = True code isn’t working.
When I delete these everything else is fine.
Not sure what I did wrong but can you help one more time.
Jon Peltier says
Are there data labels?
Wes says
Just thought of that myself.
Some lines have data labels some do not. And it is preferable to keep it that way. Is that possible?
Jon Peltier says
So you need something like this:
Wes says
Jon,
One last thing, is there a reason that the VBA code pulls the color either lighter or dark then it would usually show if I selected the color? for Instance Purple Accent 4, Lighter 80% looks kind of gray when pulled. not a huge deal I’m ready to look past it but I just thought I’d ask.
Jon Peltier says
The code in this tutorial all uses ColorIndex, which has nothing to do with the color picker introduced in Excel 2007.
I’d suggest you experiment with the macro recorder while you format some dummy chart series, and see what the effect of .Brightness is on the resulting color. Ignore .TintAndShade, because that’s broken.
Wes says
Jon,
So after 4 days of research and figuring out how to write this code since the colors will not come out like the selected colors on the color picker in excel the team doesn’t accept the report! What a waste! Well I would like to paste the final code so maybe it can help someone else in the future trying to do something similar.
Again Jon thank you for all your help!
Rshah says
Jon Peltier says
Replace
with
Mark says
Hi John,
Great, thank you, this is exactly what I need, except I am trying to change the colours of a simple bar chart that has been created in Powerpoint (2011). How do I select an active chart that is in the pptx while looking at the data that is in excel?
Thanks again,
Mark
Jon Peltier says
Hi Mark –
I’m not ignoring your comment, it’s a very good point.
I’m preparing software to work together with Excel and PowerPoint, and I will be preparing some tutorials on related topics. In addition I will catch up tutorials like this one with extensions to PowerPoint-based charts.
Terry Ginzburg says
Hi John,
This is great information, so thank you. I’ve got a set of charts with related data (e.g. job roles), and am using this to control color so the color for each data element is consistent. It’s working on my column charts perfectly, but on the line charts (using the code you provided above) it runs and changes the colors, but the lines themselves end up a different color than the one in my range. It’s getting the right color, but I think the problem is that the .Border code is setting the outline of the line, not the fill. When I use go to the chart myself and set the color manually, it’s the Outline color that is actually filling the line (not sure why that is, but that’s the way it seems to work).
My problem is that I can’t seem to find the right code to set the ‘Outline’ color on the lines. Can you help?
Here is my code, I think I just need to add a line below .Border… to address the outline of the line.
Thank you so much!
Terry
Jon Peltier says
Terry –
Try this first. The old ColorIndex approach is based on the color system used Excel 2003 and earlier, and is not reliable in more recent versions. Change
.ColorIndexto.Colorand see if that fixes the issue.Terry Ginzburg says
Thank you, Jon, worked like a charm!
Deepthi says
Hi Jon,
I tried the same on a graph created on a Pivot table and consequently want to change the colours or my dash board too. Is it possible that that I can define my chart in the code- like chart 1 or so and then use the code rather than having to click on a macro button each time since a dashboard would have multiple graphs of the same type?
Thanks!!
Deepthi
Cezara says
Hello Jon,
This VBA code is very useful for me. Just wanted to let you know that I do not know much in this field ..
Now I had to create a chart with a variable data range (a colleague helped with the code – written below) and I don’t know how to adapt your formatting solution here.
Thank you,
Cezara
Jon Peltier says
Right after
SetSourceData, you might incorporate something like this code, taken from the last example in the post:Cezara says
Thank you, I will try this.
Christian Bahnsen says
Well done! I used your code almost verbatim to come up with a solution for a question I’d posted on the Excel IT Pro forum: https://social.technet.microsoft.com/Forums/office/en-US/7eb70726-c5de-48e8-8ba7-bb1edf94867b/excel-2010-how-can-i-specify-the-color-to-use-for-each-series-in-a-stacked-bar-chart?forum=excel
martina says
I would like to use this code to only change the colours of one series which I naned “status” when creating the series. I cannot get it to work. I get the error:
Object variable or width variable not set. Please help.
Sub ColorByCategoryLabel()
Dim rPatterns As Range
Dim iCategory As Long
Dim vCategories As Variant
Dim rCategory As Range
Worksheets(“sheet1”).ChartObjects(“chTasks”).Select
Set rPatterns = ActiveWorkbook.Worksheets(“sheet1”).Range(“L1:L6”)
Worksheets(“sheet1”).ChartObjects(“chTasks”).Select
With ActiveChart.SeriesCollection(“status”)
vCategories = .XValues
For iCategory = 1 To UBound(vCategories)
‘MsgBox (“I category ” & iCategory)
Set rCategory = rPatterns.Find(What:=vCategories(iCategory))
Error here- ActiveChart.SeriesCollection(“status”).Points(iCategory).Interior.ColorIndex = rCategory.Interior.ColorIndex
Next
End With
End Sub
Jon Peltier says
Martina –
This is more closely related to my other post, VBA Conditional Formatting of Charts by Category Label. In addition, unless you’re still using Excel 2003 or earlier, you should be using the syntax with Color not ColorIndex.
But in any case, the code doesn’t fail if there is a sheet named “sheet1”, there is a series named “status”, and the category (X value) for each point is contained withing the range rPatterns.
kelvin chitambo says
Hi Jon
I have followed your work here and i find it very enlightening
Are you able to apply conditional formatting on a pivot chart???
Jon Peltier says
Kelvin –
These VBA procedures will work on a Pivot Chart. Pivot Charts are notorious, though, for reverting customized formatting to default, so you may have to rerun the code frequently.
MRS says
Hey Jon nice work, it has been extremely helpful!
Should this VBA coding also work in line charts? Or should I use the category code?
Thanks a lot!
Antonio says
Hi Jon,
I have been able to use successfully this amazing code (congrats!!!) onto a pivot chart successfully, … but if series name is based on more than a single variable of the underlying pivot table, I cannot control based on which variable of the name I am coloring. I have checked (using a “watch” in VBA debug to check rSeries/iSeries) that mulple variable based name of the series are captured by the code if in rPatterns such combination exists .
I am doing line chart with year in X-axis, one line per serie. I attach two examples (real data is huge, 6 dimensions for series names instead of just two as in the example).
Toy example b) Pivot on which the pivot chart is based:
EU EU USA USA
SecA SecB SecA SecB
2000 1 2 4 8
2010 3 5 7 9
2020 5 9 7 5
If I add labels with series names, automatic series names added are EU – SecA, EU – SecB, USA – SecA, USA – SecB.
Ideally, I would like to be able to control with which part of the label (EU/USA or SecA/SectB) I can color the series: i.e. I would like to color the series of a country always in the same color and difference across Sec using different markers.
Thanks a lot before-hand!
Jon Peltier says
Antonio –
You can set up a color-coded range using just the country names, and modify the search in the VBA routine to find just the first part of the series name in this range.
Antonio says
Supercool Jon! Thanks!
David says
Hi John,
thank you for your instructional blog.
I have a line graph showing many series.
The names of the series contain the category value but also a number.
Cat1 S1, Cat1 S2, Cat1 S4, Cat2 S1, Cat2 S2, Cat3 S1 …. Etc
3 categories, many series each.
How would I change your code so that it looked for the category withing the name?
Alternatively, referenced a set of cells to find the category?
Many thanks in advance.
Jon Peltier says
David –
I might try something like this to use the first part of the series name (up to the space):
David says
That’s great thank it works perfectly.
Is it possible to do the same for a line graph?
David Thomson says
I managed to find the right syntax.
Thanks very much for your help! Much appreciated!
This includes syntax for Line, Fill and Glow.
Sub ColorBySeriesName()
Dim rPatterns As Range
Dim iSeries As Long
Dim rSeries As Range
Set rPatterns = ActiveSheet.Range(“A256:A258″)
With ActiveChart
For iSeries = 1 To .SeriesCollection.Count
Dim sFindText As String
sFindText = .SeriesCollection(iSeries).Name
If InStr(sFindText, ” “) > 0 Then
sFindText = Left$(sFindText, InStr(sFindText, ” “) – 1)
End If
Set rSeries = rPatterns.Find(What:=sFindText, LookAt:=xlWhole)
If Not rSeries Is Nothing Then
.FullSeriesCollection(iSeries).Format.Fill.ForeColor.RGB = _
rSeries.Interior.Color
.FullSeriesCollection(iSeries).Format.Line.ForeColor.RGB = _
rSeries.Interior.Color
.FullSeriesCollection(iSeries).Format.Glow.Color.RGB = _
rSeries.Interior.Color
End If
Next
End With
End Sub
David Thomson says
This only works if I run the macro from inside the editor.
If i try to assign a button I get run time error 91 “object variable or block variable not set”
How would I set this so that the chart changed when the user changed the colour of the cells?
Or alternatively, hit a button after having changed the cells?
Many thanks again.
Jon Peltier says
David –
The code is looking for the active chart. Make sure you select a chart before clicking the button.
If you are using an ActiveX button (I usually recommend not using ActiveX buttons), you also have to change its TakeFocusOnClick property to False.
David Thomson says
Ah! So simple!
Thank you so much for this!
Select the cell colours, click the graph, click the button and hey presto, it changes.
On the other hand, it makes for a rather weird user experience.
Is there no way to bypass the ‘click the graph’ part of this?
Jon Peltier says
David –
Instead of
you can reference a chart named “Chart 1” using
Oliver says
Hi Jon,
Really useful code and almost exactly what I need but like some others, I am struggling to get this to work with a Pivot Line Graph.
I have tried to use code shared in other comments but to no avail – I am hoping you can help?
Similar to Antonio earlier, my series data is based on two variables from the Pivot Table (transaction / sale number and sale rating e.g. number 5 – poor) tracked across time (X axis).
I have a line chart with each sale on it and I’m needing to colour them grouped by their rating (good, strong etc). Series labels are created on the chart as “Trade X – Rating” e.g. “Trade 1 – Poor”.
I tried the following code you recommended for Antonio but with no luck. I get no errors but the colours do not change. My “key” is E1 – I4 (Negative, Poor, Neutral, Good, Strong) coloured appropriately.
I tried reversing hte data so it had the rating first (e.g. Strong – Trade 1) as the below code seems to look for a space (sFind = Left$(sName, InStr(sName, ” “) – 1)) and -1, taking the characters from the left but this didn’t work for me either…
Really hope you can help, this would be super useful if I can get it work!
Thanks
Jon Peltier says
Oliver –
The code you’ve cited changes the fill color. A line chart without markers has no fill, just a line. Change this:
to this:
and see if it works.
Matthew Davey says
I can not seem to get to work if the colour cells contain an equation which fetches the name.
The code fails to match the name with the series even though the chart series & and colour cell both link to the same cell which contains the name. I have tried everything I can think of. Any suggestions, gratefully received! My code is as below:
Sub ColorBySeriesName()
Dim rPatterns As Range
Dim iSeries As Long
Dim rSeries As Range
Set rPatterns = Worksheets(“LISTS”).Range(“R4:R5”)
Set rPatterns = ActiveSheet.Range(“AG6:AG18”)
With ActiveSheet.ChartObjects(“Chart_Estimated”).Chart
For iSeries = 1 To .SeriesCollection.Count
Set rSeries = rPatterns.Find(What:=.SeriesCollection(iSeries).Name, _
LookAt:=xlWhole)
If Not rSeries Is Nothing Then
.SeriesCollection(iSeries).Format.Fill.ForeColor.RGB = _
rSeries.Interior.Color
.SeriesCollection(iSeries).Format.Line.ForeColor.RGB = _
rSeries.Interior.Color
End If
Next
End With
End Sub
Matthew Davey says
OK may have found a way around the equation driven cell error….. by running a macro that copies the equation cells and paste special (values) into the next column along which also contains the colours. Set this column as the Range and everything works as it should.
Next question though…..is there a way to modify the code so that instead of looking at the same cell as the text match for the associated fill colour, could it be made to look at the neighbouring cell instead. So including an Offset perhaps??
Ati says
Hi Jon,
I have tried to use your code, but it is not working for me. Please could you advise what could be the problem.
R1:R7 includes the colored cells with the names which I would like to use to color the chart series. (they are on the same page as the chart.
I click on the chart on the page before I run the macro, but nothing happen.
I m using excel for office 365
Thank you in advance
Sub ColorBySeriesName()
Dim rPatterns As Range
Dim iSeries As Long
Dim rSeries As Range
Set rPatterns = ActiveSheet.Range(“R1:R7”)
With ActiveChart
For iSeries = 1 To .SeriesCollection.Count
Set rSeries = rPatterns.Find(What:=.SeriesCollection(iSeries).Name, LookAt:=xlWhole)
If Not rSeries Is Nothing Then
.SeriesCollection(iSeries).Format.Fill.ForeColor.RGB = rSeries.Interior.Color
End If
Next
End With
End Sub
Attila says
Hi Jon,
I have debug the code and the issue is with
Set rSeries = rPatterns.Find(What:=.SeriesCollection(iSeries).Name, LookAt:=xlWhole)
When it gets the name from the chart, it is adding a space before and after the name and after it cannot find it from my list as there is no space before and after the name. Im not sure why it is happening as the chart was created with series names where no space before and after…
Any suggestion?
Thank you
Ati says
Hi Jon,
I have amended the code using Trim, as per below. And it is working fine now.
Set rSeries = rPatterns.Find(What:=Trim(.SeriesCollection(iSeries).Name), LookAt:=xlWhole)
Im just not sure why it was adding spaces before…
Anyway. Thanks for the great code. Nice and simple.
Jon Peltier says
Hi Ati –
You did a good job of debugging the problem. I saw your first comment and thought, Make sure the series names match the formatting lookup labels exactly, including blank spaces, which are hard to notice. Then I watched you find the mismatch, and finally use Trim to solve it.
The series names probably come from other cells than the lookup range, or perhaps they are entered by hand. These conditions may result in the discrepancies you’ve encountered.
Monica says
I .looking for conditionally formatting a chart based on a combination of series and category name. I am working on stacked bar chart sbd for different series samd category color should be different. Can you please help.
Monica says
I am looking for conditionally formatting a chart based on a combination of series and category name. I am on stacked bar chart and for different series but same category color should be different. Can you please help.