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 Jon Peltier
Time: Tuesday, March 4, 2008, 8:40 pm

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.

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 Jon Peltier
Time: Wednesday, March 5, 2008, 7:09 am

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.

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 Jon Peltier
Time: Monday, March 17, 2008, 10:26 am

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.

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 Jon Peltier
Time: Monday, March 17, 2008, 11:19 am

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.

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 Jon Peltier
Time: Wednesday, April 23, 2008, 6:56 am

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.

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 Jon Peltier
Time: Tuesday, May 13, 2008, 2:14 pm

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.

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 Jon Peltier
Time: Tuesday, May 13, 2008, 5:04 pm

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?

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 Jon Peltier
Time: Wednesday, May 14, 2008, 12:32 pm

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 If