change series color in Excel VB

[Dave Dowell]

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("a1138"), _
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

 

[Jon Peltier]

Dave -

Your command

.SeriesCollection(1).ColorIndex = 3

does not work for two reasons. One, it needs only one dot, although I
assume the two dots was a typo in the newsreader. The other reason is
that the Series object does not have a color property.

The second macro you put up is obviously a recorded macro, but there is
no sign that you tried reformatting the series while the recorder was
running.

I just turned on the macro recorder, and changed the line color of a
series. Here is the macro I got:

' Macro2 Macro
' Macro recorded 8/19/2004 by Jon Peltier
'
ActiveChart.SeriesCollection(1).Select
With Selection.Border
.ColorIndex = 3
.Weight = xlThin
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgroundColorIndex = xlAutomatic
.MarkerForegroundColorIndex = xlAutomatic
.MarkerStyle = xlAutomatic
.Smooth = False
.MarkerSize = 5
.Shadow = False
End With
End Sub

All that xlAutomatic stuff wasn't changed in the macro, else it wouldn't
still be xlAutomatic. The part of the macro that did anything was

ActiveChart.SeriesCollection(1).Select
With Selection.Border
.ColorIndex = 3
End With

since I didn't change the weight or style of the line. This can be
streamlined to:

ActiveChart.SeriesCollection(1).Border.ColorIndex = 3

So through a little sleuthing, we find that the line of a line chart is
actually the series border (not obvious a priori), and we learn the
correct syntax to change its color.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[randomb]

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("a1138"), _
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