Changing ChartTypes will not change the color of the Series

I have Excel 2007 and trying to make this compatible with users that
have Excel 2002/2003.

I am having a hard time with the code below. When I change a specific
series from a stacked chart to a line and then try to change the
color, I am not successful. I am successful with changing the chart
type, however.

'Create the Average Line
.ActiveChart.PlotArea.Select
.ActiveChart.SeriesCollection.NewSeries
y = .ActiveChart.SeriesCollection.Count
.ActiveChart.SeriesCollection(y).Values = "=Sheet2!R" & intRow
+ 1 & "C2:R" & intRow + 1 & "C" & rst.Fields.Count
.ActiveChart.SeriesCollection(y).Name = "=""Average"""
.ActiveChart.SeriesCollection(y).Select
.ActiveChart.SeriesCollection(y).AxisGroup = 1
.ActiveChart.SeriesCollection(y).ChartType = 4
.ActiveChart.SeriesCollection(y).Interior.Color = RGB(0, 0, 0)

I have tried everyhing and I can't figure it out. Any help you can
shed will be greatly apprecitated.

A line chart series has no Interior. Try MarkerBackgroundColor and
related properties.

Best approach when you're stuck on syntax is to open Excel 2003, turn on
the macro recorder, do what you want you program to do, and make use of
the recorded code.

- Jon
-------
Jon Peltier
Peltier Technical Services, Inc.
http://peltiertech.com/


On 3/8/2010 10:48 AM, EAB1977 wrote:
I have Excel 2007 and trying to make this compatible with users that
have Excel 2002/2003.

I am having a hard time with the code below. When I change a specific
series from a stacked chart to a line and then try to change the
color, I am not successful. I am successful with changing the chart
type, however.

'Create the Average Line
.ActiveChart.PlotArea.Select
.ActiveChart.SeriesCollection.NewSeries
y = .ActiveChart.SeriesCollection.Count
.ActiveChart.SeriesCollection(y).Values = "=Sheet2!R"& intRow
+ 1& "C2:R"& intRow + 1& "C"& rst.Fields.Count
.ActiveChart.SeriesCollection(y).Name = "=""Average"""
.ActiveChart.SeriesCollection(y).Select
.ActiveChart.SeriesCollection(y).AxisGroup = 1
.ActiveChart.SeriesCollection(y).ChartType = 4
.ActiveChart.SeriesCollection(y).Interior.Color = RGB(0, 0, 0)

I have tried everyhing and I can't figure it out. Any help you can
shed will be greatly apprecitated.

Try something like this:
ActiveChart.SeriesCollection(y).Border.ColorIndex = 14


Yes, you need a good old colour index value to set this (if you need to
retain compatibility as you have said).

Jon P has some useful thoughts on palettes and a utility to change them
so you can get sensible colours too, from his website linked below.

Hope this helps

Adam

On 09/03/2010 14:59, Jon Peltier wrote:
A line chart series has no Interior. Try MarkerBackgroundColor and
related properties.

Best approach when you're stuck on syntax is to open Excel 2003, turn on
the macro recorder, do what you want you program to do, and make use of
the recorded code.

- Jon
-------
Jon Peltier
Peltier Technical Services, Inc.
http://peltiertech.com/


On 3/8/2010 10:48 AM, EAB1977 wrote:
I have Excel 2007 and trying to make this compatible with users that
have Excel 2002/2003.

I am having a hard time with the code below. When I change a specific
series from a stacked chart to a line and then try to change the
color, I am not successful. I am successful with changing the chart
type, however.

'Create the Average Line
.ActiveChart.PlotArea.Select
.ActiveChart.SeriesCollection.NewSeries
y = .ActiveChart.SeriesCollection.Count
.ActiveChart.SeriesCollection(y).Values = "=Sheet2!R"& intRow
+ 1& "C2:R"& intRow + 1& "C"& rst.Fields.Count
.ActiveChart.SeriesCollection(y).Name = "=""Average"""
.ActiveChart.SeriesCollection(y).Select
.ActiveChart.SeriesCollection(y).AxisGroup = 1
.ActiveChart.SeriesCollection(y).ChartType = 4
.ActiveChart.SeriesCollection(y).Interior.Color = RGB(0, 0, 0)

I have tried everyhing and I can't figure it out. Any help you can
shed will be greatly apprecitated.