Apply macro to all but two series in charts

[benjthom]

Hi all,

I am using the following code to automatically update the colour of my chart's lines to the same colour as the cells. However there are 33 series in each of my charts and I only want the code to apply to series 1 - 31 and not series 32/33.

Dim MySeries As Series
Dim FormulaSplit As Variant
Dim SourceRange As Range
Dim SourceRangeColor As Long

'Loop through all charts in the active sheet
For Each oChart In ActiveSheet.ChartObjects

'Loop through all series in the target chart
For Each MySeries In oChart.Chart.SeriesCollection

'Get Source Data Range for the target series
FormulaSplit = Split(MySeries.Formula, ",")

'Capture the first cell in the source range then trap the color
Set SourceRange = Range(FormulaSplit(2)).Item(1)
SourceRangeColor = SourceRange.DisplayFormat.Interior.Color

On Error Resume Next

'Coloring for Excel 2007 and 2010
MySeries.MarkerBackgroundColor = SourceRangeColor
MySeries.MarkerForegroundColor = SourceRangeColor
MySeries.Format.Line.ForeColor.RGB = SourceRangeColor
MySeries.Format.Line.BackColor.RGB = SourceRangeColor
MySeries.Format.Fill.ForeColor.RGB = SourceRangeColor
MySeries.Format.Line.Weight = 1.5
MySeries.Format.Line.DashStyle = 4
MySeries.Format.Line.Style = msoLineSingle
MySeries.MarkerStyle = xlMarkerStyleNone

Next MySeries
Next oChart

End Sub

Any help would be greatly appreciated.

Cheers,

Ben

[Trebor76]

Hi benjthom,

Post back re how this goes:

Please Login or Register  to view this content.

If could also wrap your code in the appropriate tags as I have done, it would be appreciated.

Regards,

Robert

[benjthom]

Hi Robert,

I tried using your code but it still applied the macro to all of the series in my charts. An alternative solution I found on /r/excel is provided below. Thanks for your help anyway.

Please Login or Register  to view this content.

Regards,

Ben

[Trebor76]

See if changing the Or part of this statement...

Please Login or Register  to view this content.

...to And makes a difference.

Robert