Set color for chart serie in excel 2007/2010

[Lina]

Hi!
I want to do a macro that sets the color for the chart series to the color
the company has desided to use. We have a theme but we can only set color for
the first 6 series and we often use much more series.
I have understod that this isnt so easy as to loop thru the series and set
the color since the color is set on different ways depending on the charttype
for the serie, or is it a way to do this so it works for all chart types?

thanks

 

[Peter T]

As you say only six basic colours are available. However each subsequent set
of six is a shade lighter, although each incremental difference in shade is
not very noticeable from the previous set.

One approach is to define at least as many colours in RGB format as you'll
need, then a simple macro can apply them in order to your series. Post back
if not sure how to go about that, and say how many colours you'll need.

Regards,
Peter T

 

[Lina]

Hi,
Thank you for your answer and maybe you can help me a bit further. I have to
rewrite some code that was written for excel xp and that doesnt work so god
(for many different reasons) The old code uses customcharts
(xxx.Chart.ApplyCustomType ChartType:=xlUserDefined, TypeName:="test") but I
understand that it's better to use the new chartstyles (is it?) since the
customcharts not will be supported further on (is this right?)

I would like to make a button that loops all series in the selected chart
and changes the color. My problem is that I must handle each diagram type of
serie and Im not sure how to do this the best way so that I cover as many
charttypes as possible without having runtime error.

thanks

 

[Peter T]

In Excel 2007 customs charts are saved as chart templates. Manually look at
Change ChartType, Templates. In VBA help look at AddChartAutoFormat and
ApplyChartTemplate

Your other question is very vague, try and explain with some detail. Eg, do
you have a set of colours, what ChartType are your series, eg bar or line.
and looking to change fill/interior or border/line.

Regards,
Peter T

 

[Lina]

The company has a set of color and they dont allow any other colors. I
have done some chart templates with the predifined colors. The problem
is that if a user adds more data to a chart that is allready done the
new serie will not use the color that is saved in the chart template,
it uses the built in colors instead. Do get the right colors you can
apply the chart template again but, if the user has made som settings
for the chart thoose will be overriden by the template. So instead I
would like to have a button "set company color" and that button should
go thru the series and set the color that is allowed by the company. I
would like it to work for all type of charts. I have it working for
bar, line and pie but what if the user has made a bar and a line, is
there a way to get this to work for all chart types or must I treat
each type of serie differently?

 

[Peter T]

I'm not sure I quite follow. Indeed the code is slightly different to apply
colour to Line or Fill but you say you have already got the code to do that.
The code is also slightly different in 2007+ and earlier versions.

For earlier versions have you considered making a workbook template for all
users with a customized palette. In particular customize the 6th row for
fill colours and the 7th row of 8 for line colours. If you do that all
charts will be automatically applied with your default colours (at least for
the first 8 series).

That approach is not useful in 2007. You can of course apply your own Office
theme. Colours are the same for fills and lines (unlike earlier versions).
Each set of series uses a different shade of them colour.

Another way is to defile your own palette, one way - (this assumes 2007)

- Colour format some cells, at least as many as there will ever be series in
a chart, Ctrl-F1, Fill, More colors..
- Name the coloured cells say "Swatch"

In your code that colours the series start with this

Dim pal() as long
getColors pal

and add this function

Sub getColors(pal() As Long)
Dim i As Long
Dim rng As Range, c As Range
Set rng = ActiveWorkbook.Names("Swatch").RefersToRange
ReDim pal(1 To rng.Count)
For Each c In rng
i = i + 1
pal(i) = c.Interior.Color
Next
End Sub


Do you know know to process each chart on each sheet?

Regards,
Peter T