Multiple series with the same format

[Yago]

Hi, I'm trying to plot a chart with more than 800 series. The problem
is that I want all the series to have the same format(color, weight
and line type). I have to do about 5 of these, so changing each series
to the same format can be really cumbersome. Is there a way to select
all the series and give them the same format?

Thanks


Yago

 

[Jon Peltier]

Yago -

Format the first series the way you want them all to look, then select
the chart and run this macro:

Sub FormatAllSeriesLike1()
Dim srs As Series, srs1 As Series
Set srs1 = ActiveChart.SeriesCollection(1)
For Each srs In ActiveChart.SeriesCollection
With srs.Border
.ColorIndex = srs1.Border.ColorIndex ' 1
.Weight = srs1.Border.Weight ' xlThin
.LineStyle = srs1.Border.LineStyle ' xlContinuous
End With
With srs
.MarkerBackgroundColorIndex = _
srs1.MarkerBackgroundColorIndex ' 2
.MarkerForegroundColorIndex = _
srs1.MarkerForegroundColorIndex ' 1
.MarkerStyle = srs1.MarkerStyle ' xlSquare
.Smooth = srs1.Smooth ' False
.MarkerSize = srs1.MarkerSize ' 5
.Shadow = srs1.Shadow ' False
End With
Next
End Sub

- Jon

 

[Yago]

Hi, thanks for your help, but I'm new to macros and don't
know how to use it. I pasted it in the visual basic
editor and when I try to run it I get a 1004
error: "can't otbtain the property Color Index from the
class Border" (I'm using a spanish version and transalted
it the best I could). Am I doing something wrong?

Thanks again

Yago

 

[Jon Peltier]

Yago -

I've had nearly no experience with different language versions of Excel.
Here's what I would tell an English version user about macros.

Press Alt-F11 to open the VB Editor. Select Module from the Insert
menu, and paste the code there.

Back in Excel, select a chart and run the macro.

What I didn't mention was that my macro was constructed for Line or
Scatter charts. For a Column or Bar chart, you'd need to adjust the
properties of that chart type:

Sub FormatAllSeriesLike1()
Dim srs As Series, srs1 As Series
Set srs1 = ActiveChart.SeriesCollection(1)
For Each srs In ActiveChart.SeriesCollection
With srs.Border
.ColorIndex = srs1.Border.ColorIndex ' 1
.Weight = srs1.Border.Weight ' xlThin
.LineStyle = srs1.Border.LineStyle ' xlContinuous
End With
With srs.Interior
.ColorIndex = srs1.Interior.ColorIndex ' 3
.Pattern = srs1.Interior.Pattern ' xlSolid
End With
With srs
.InvertIfNegative = _
srs1.InvertIfNegative ' 2
.Shadow = srs1.Shadow ' False
End With
Next
End Sub

But this one has a border, so there might be a language issue at work.
What kind of chart do you have?

- Jon

 

[Guest]

Jon,

My chart is an X-Y plot. Is that a scatter plot?

 

[dvt]

Jon,

My chart is an X-Y plot. Is that a scatter plot?

In English versions of Excel, it's called an "XY (Scatter)" chart. So I
think the answer is yes.

 

[Jon Peltier]

In which case, I can be of no further help.

- Jon

 

[Jon Peltier]

Yago -

One more idea. Is there a series in the chart that isn't charted? For
example, it points to a blank range, or a range of errors (#N/A)? You
can't access the series formula of such a series, so maybe you can't get
to its formats.

- Jon

 

[Yago]

That was the problem. I had left one space between the X
Axis values and the rest of the data.

Thanks a lot


Yago

 

[Jon Peltier]

Glad to help you straighten it out.

- Jon