change series color in Excel VB
 

Dave Dowell wrote:
Having created a chart with the VB chartwizard, I want to
control the colors of the series lines (not the
markers). Can anyone tell me how to do that? Below is
the basic code I am using. Using for example

With ActiveChart
.SeriesCollection(1).ColorIndex = 3
End With

does not work.

Thanks in advance. DD


Dim ch As ChartObject
Set ch = Worksheets(wksh).ChartObjects.Add(0, 0, 700,
500)
ch.Chart.ChartWizard Source:=Worksheets
("COLLEGEDATA").Range("a1:o138"), _
gallery:=xlLine, Format:=4, Title:=wksh & "
Enrollment", _
PlotBy:=xlColumns, CategoryLabels:=1,
SeriesLabels:=1, CategoryTitle:="Date", ValueTitle:="FTES"
ch.Activate
With ActiveChart
.WallsAndGridlines2D = True
.Axes.Item(xlCategory).CategoryType = xlTimeScale
.Axes(xlCategory).TickLabels.NumberFormat = "m/d"
.PlotArea.Interior.ColorIndex = 15
.Axes(xlValue).MinimumScale = 0
.Axes(xlValue).MaximumScale = mx
.Legend.Position = xlLegendPositionRight
.DisplayBlanksAs = xlInterpolated
.Legend.Top = 0
.PlotArea.Top = 15
.PlotArea.Left = 0
.ChartTitle.Top = 0
End With

I am not versed in VB (I work with C++) so my attempts at this were
tough (and the following code is quite ugly and can be improved by
others). I had a cross-tab graph with a bunch of series. Basically I
wanted a gradual change in color from one end of the spectrum to the
other. To do this, I came up with the following (using MS-Access 2002,
but should have an equivalent with other office products). The Chart /
Graph is in a Form.

Function a()
Set bbb = Application.Forms("Form1")("OLEUnbound3")
Div = Fix(255 / bbb.SeriesCollection.Count)
For a = 1 To bbb.SeriesCollection.Count
bbb.SeriesCollection(a).Interior.Color = RGB(a * Div, 255 - (a *
Div), 0)
bbb.SeriesCollection(a).Border.Color = RGB(a * Div, 255 - (a *
Div), 0)
bbb.SeriesCollection(a).Border.LineStyle = xlNone
bbb.SeriesCollection(a).MarkerSize = 2
Next a

End Function