Comment from Tony
Time: Tuesday, March 4, 2008, 8:28 pm

Jon - I’m not a huge fan of the color choices, but it’s a nice trick that is pretty straightforward. Nice work!

Comment from John Mansfield
Time: Wednesday, March 5, 2008, 12:26 am

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.

Comment from John Mansfield
Time: Wednesday, March 5, 2008, 12:30 am

Jon,

Sorry, it looks like I didn’t pick up the “pre” tag. Hopefully this works . .

< pre > Your code here < / pre >

Comment from Davie
Time: Monday, March 17, 2008, 9:33 am

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

Comment from Davie
Time: Monday, March 17, 2008, 11:08 am

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

Comment from Davie
Time: Tuesday, March 18, 2008, 4:22 am

Jon, it is a stacked column graph. I tried “MarkerBackgroundColorIndex but still got the same error message.

Davie

Comment from Davie
Time: Wednesday, March 19, 2008, 5:31 am

Jon, i recreated the graph and it is working ok. Don’t know what the problem was. Thanks for your help.

Davie

Comment from tom
Time: Wednesday, April 23, 2008, 2:15 am

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

Comment from Drandon Campbell
Time: Tuesday, May 13, 2008, 1:17 pm

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.

Comment from Drandon Campbell
Time: Tuesday, May 13, 2008, 2:45 pm

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.

Comment from Drandon Campbell
Time: Wednesday, May 14, 2008, 12:14 pm

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.

Comment from ariella
Time: Wednesday, August 13, 2008, 10:26 am

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?

Comment from ckz
Time: Monday, September 8, 2008, 3:02 pm

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

Comment from mikael
Time: Monday, September 8, 2008, 6:41 pm

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?

Comment from john
Time: Tuesday, October 21, 2008, 9:11 pm

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

Comment from john
Time: Tuesday, October 21, 2008, 11:20 pm

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

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.

Comment from john
Time: Wednesday, October 22, 2008, 7:55 am

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.”

Comment from john
Time: Wednesday, October 22, 2008, 2:17 pm

Thanks for your help.

Comment from john
Time: Wednesday, October 22, 2008, 2:56 pm

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.