Excel 2010 Macro Format Line Settings

 

The LineFormat object formats either a line or the border around an object. You can change many properties for a line, such as the color, arrows, dash style. etc.

The following macro formats the trendline for the first series in a chart:

Sub FormatLineOrBorders()

    Dim cht As Chart

    Set cht = ActiveChart

    With cht.SeriesCollection(1).Trendlines(1).Format.Line

        .DashStyle = msoLineLongDashDotDot

        .ForeColor.RGB = RGB(50, 0, 128)

        .BeginArrowheadLength = msoArrowheadShort

        .BeginArrowheadStyle = msoArrowheadOval

        .BeginArrowheadWidth = msoArrowheadNarrow

        .EndArrowheadLength = msoArrowheadLong

        .EndArrowheadStyle = msoArrowheadTriangle

        .EndArrowheadWidth = msoArrowheadWide

    End With

End Sub

 

When formatting a border, the arrow settings are not relevant, so the code is shorter than the code for formatting a line.

The following macro formats the border around a chart:

Sub FormatBorder()

    Dim cht As Chart

    Set cht = ActiveChart

    With cht.ChartArea.Format.Line

        .DashStyle = msoLineLongDashDotDot

        .ForeColor.RGB = RGB(50, 0, 128)

    End With

End Sub