VBA Macro 100+ Line Chart

[KingGoulash]

Hey everyone, new to the forum but have been reading up on a lot of these VBA macros. I currently am running a database of about 600+ lines with x-axis being time and y-axis being score. When you make a multiple lines graph in excel, every single line is formatted differently. I was looking to utilize VBA for what I believe is some pretty simple code but when I try to do it myself, I cannot seem to get it to run properly. Basically, I have all the data already in a graph with each line series but excel has changed every line to be a different color, every marker to be different and every fill to be different.... I simply want to format every single line to have the following characteristics but when i try to do it in VBA it fails.

I will be extremely thankful if someone can figure out a VBA macro for a multiple stacked line chart, that changes every line the selected chart to the following parameters since it has been taking me about 6 hours per chart to change every single line individually. I will greatly appreciate the help!

 

[Domenic]

I couldn't tell whether the desired color is black or dark blue, so I went with dark blue. Change the color as desired.

Sub FormatSeriesCollection()

    Dim chrt As Chart
    Set chrt = ThisWorkbook.Worksheets("Sheet1").ChartObjects("Chart 1").Chart 'change the workbook, worksheet, and chart names accordingly
    
    Dim sr As Series
    For Each sr In chrt.SeriesCollection
        With sr
            .Format.Line.ForeColor.RGB = RGB(0, 32, 96) 'dark blue
            .Format.Line.Weight = 1
            .MarkerSize = 5
            .MarkerStyle = xlMarkerStyleCircle
            .MarkerForegroundColor = RGB(0, 32, 96) 'dark blue
            .MarkerBackgroundColor = RGB(255, 255, 255) 'white
        End With
    Next sr

End Sub

Hope this helps!

 

[KingGoulash]

This worked awesome! I cant believe I gave myself arthritis doing the F4 method haha. Last question would be, for the marker background color, what is the RGB to do no fill?

 

[Domenic]

Try...

.MarkerBackgroundColor = -2

Hope this helps!