Repeating colors in excel 2007 area charts

[jon18]

I'm in the process of converting dynamically generated excel charts from
Excel 2003 to 2007. A couple problems I have resolved, but so far have not
been able to find a solution to a color repetition problem. The charts can
have anywhere from 2-3 legend entries to 15 -20. Problem is after the 1st
(lowest) three appear as individual colors, all the colors after that are the
same color. When this same chart is generated in 2003 no problems, 2007
repeating colors. So far I've tired to find some sort of legendkeys command
to set the colors to xlautomatic (which also works fine in 2003) but I can't
seem to find an example or the proper syntax to do this in 2007.

thanks,
jonathan

 

[Andy Pope]

Hi,

I can not reproduce the problem with colours repeating.
But this code sets the colours to automatic.

Sub x()
Dim objSeries As Series

For Each objSeries In ActiveSheet.ChartObjects(1).Chart.SeriesCollection
objSeries.Interior.ColorIndex = xlAutomatic
Next
End Sub

Cheers
Andy

 

[jon18]

perfect! I had to put in some logic (application.version) so that it only
attempts this in 2007 as it generates errors in 2003, but it fixes it right
up.
the scale is set in code on auto open, so I wonder if that is why I'm seeing
the change.

With Worksheets(1).ChartObjects(2).Chart
.Axes(xlValue).MajorUnit = 20
.Axes(xlValue).MinimumScaleIsAuto = True
.Axes(xlValue).MaximumScaleIsAuto = True
.Axes(xlValue).MinimumScale = intMinScale
.Axes(xlValue).MaximumScale = intMaxScale
End With


Anyway, this definitely takes care of the problem as my users convert to
2007.

thanks again!!
jonathan

 

[Andy Pope]

Hi,

Glad it worked although I'm confused about why you got errors in xl2003 as I
used that version to record the base code.

Changing the axis properties would not effect a Area chart but it would a
Surface chart as the scale bands determine the colours.

Cheers
Andy