Format Data Series in a Pivot Chart

[winnie123]

Hello,

I have 2 pivot charts, which both have the same data series, but depending
on the selections made not all the data series will be used.
I have recorded the code below from the macro recorderto format each series
in a particular colour.

But if say "Late" is not in the data selected, it gives me a run time error.

How can I modify the code so that it will look for each data series and if
not found go to the next data series.

Sub FormatSeries ()

Application.ScreenUpdating = False

ActiveChart.PlotArea.Select
With Selection.Border
.ColorIndex = 39
.Weight = xlThin
.LineStyle = xlContinuous
End With
Selection.Fill.OneColorGradient Style:=msoGradientHorizontal,
Variant:=1, _
Degree:=0.231372549019608
With Selection
.Fill.Visible = True
.Fill.ForeColor.SchemeColor = 15
End With




ActiveChart.SeriesCollection("Early").Select

With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.Shadow = False
Selection.InvertIfNegative = False
With Selection.Interior
.ColorIndex = 27
.Pattern = xlSolid
End With
ActiveChart.SeriesCollection("Late").Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.Shadow = False
Selection.InvertIfNegative = False
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
ActiveChart.SeriesCollection("OnTime").Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.Shadow = False
Selection.InvertIfNegative = False
With Selection.Interior
.ColorIndex = 4
.Pattern = xlSolid
End With

Application.ScreenUpdating = True



End Sub

Any help/assistance would be greatly appreciated.

Winnie

 

[winnie123]

I have realised this is more trouble than it is worth, so have decided to
create the graphs as non pivot.That way I will keep the formatting.

Thanks

 

[Jon Peltier]

You could use this:


Dim srs As Series

For Each srs In ActiveChart.SeriesCollection
Select Case srs.Name
Case "Early"
' formatting for Early series
Case "Late"
' formatting for Late series
Case "On Time"
' formatting for On Time series
Case Else
' formatting for any other series
' or just omit
End Select


You can also improve the efficiency of your code if you don't select
objects. Change this:

ActiveChart.SeriesCollection("Late").Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.Shadow = False
Selection.InvertIfNegative = False
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With

to this:

With ActiveChart.SeriesCollection("Late")
With .Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
.Shadow = False
.InvertIfNegative = False
With .Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
End With

And since most of these settings are defaults recorded by the Macro
Recorder, you can shorten this whole thing to one line:

ActiveChart.SeriesCollection("Late").Interior.ColorIndex = 3


- Jon