Change color of a chart series as the same as another series but with different line style

Question

I am trying to make a code for changing colour of one series to match another series but with different linestyle (eg. dashed). Please see the code that I have made. I get error messages.

Thank you

Sub lineeditor()
Dim j As Integer
Dim wsheet As Worksheet
Dim cht As ChartObject
Dim serie As Series
For Each wsheet In ThisWorkbook.Worksheets
'Looping through chart in every chartobjects
    For Each cht In wsheet.ChartObjects
        cht.Activate
        cht.Select
        'Looping through second set of 9 series. totally 18 series are in the chart
        For j = 1 To 9
            cht.Chart.SeriesCollection(j + 9).Select
            With Selection.Format.Line
                .ForeColor = cht.Chart.SeriesCollection(j).Format.Line.ForeColor
                .DashStyle = msoLineDashDot
            End With
        Next
    Next
Next
End Sub

"I get error messages" is not a very useful description of what happens when you run your code. — Tim Williams, Jul 24 '20 at 06:38

score 1 · Answer 1 · answered Jul 24 '20 at 06:39

You cannot select a worksheet object that is not on the ActiveSheet. You should, however, only select or activate objects when absolutely necessary. Watch: Excel VBA Introduction Part 5 - Selecting Cells (Range, Cells, Activecell, End, Offset).

Sub lineeditor()
    Dim j As Integer
    Dim wsheet As Worksheet
    Dim cht As ChartObject
    Dim serie As Series
    For Each wsheet In ThisWorkbook.Worksheets
        For Each cht In wsheet.ChartObjects

            For j = 1 To 9
         
                With cht.Chart.SeriesCollection(j + 9).Format.Line
                    .ForeColor = cht.Chart.SeriesCollection(j).Format.Line.ForeColor
                    .DashStyle = msoLineDashDot
                End With
                
            Next
        Next
    Next
End Sub

Thank you for your answer TinMan. The series are in the same chart/chart object. I will be grateful if someone can help me with the code so that one series has the same colour has another series but with different line type. — manuel martin, Jul 24 '20 at 07:21

Change color of a chart series as the same as another series but with different line style

1 Answers1