Saturday, February 8, 2014

Format X Y Chart using VBA

Change the style and color of markers using VBA. Snapshot below -

All you need is to create a table with series name mentioned in cells and add color and marker style to cells next to it Snapshot below -

Macro to format chart -

Sub custom_markers()
Dim srs As Series
Dim cht As Chart
Set cht = Sheets("Sheet1").ChartObjects("Chart 1").Chart
    For Each srs In cht.SeriesCollection ' remove exisiting series
        'change marker color
        Select Case Range("e" & Application.WorksheetFunction.Match(srs.Name, Sheets("Sheet1").Range("a:a"), 0))
            Case "Square"
                srs.MarkerStyle = xlMarkerStyleSquare
            Case "Circle"
                srs.MarkerStyle = xlMarkerStyleCircle
            Case "Triangle"
                srs.MarkerStyle = xlMarkerStyleTriangle
        End Select
        srs.MarkerSize = 10
        srs.MarkerBackgroundColorIndex = xlColorIndexNone
        srs.MarkerForegroundColor = Range("d" & Application.WorksheetFunction.Match(srs.Name, Sheets("Sheet1").Range("a:a"), 0)).Interior.Color
        srs.Format.Line.Weight = 2
        srs.Format.Line.DashStyle = xlContinuous
    Next srs

End Sub

Download Working File

No comments:

Post a Comment