CODEWITHSUNDEEP
excelvbaexcel-charts
manuel martin
manuel martin

Reputation: 21

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

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

Upvotes: 1

Views: 89

Answers (1)

TinMan
TinMan

Reputation: 7759

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

Upvotes: 1

Related Questions