Excel VBA: Match a Trendline Label to Its Line Color

Yes. In Excel VBA, set the trendline label’s font color to the trendline’s line color: use Trendline.DataLabel.Font.Color and Trendline.Format.Line.ForeColor.RGB. The macro below does this for the first trendline on the first series in the active chart, displaying the equation label if needed.

What Excel calls a trendline label

In an Excel chart, a trendline can show its equation and, optionally, its R-squared value. Those items appear together as a trendline label on the chart; they are not ordinary worksheet cells or the data labels attached to the plotted points. In VBA, the trendline’s DataLabel object exposes formatting for that label.

Excel does not automatically make the equation text inherit the trendline’s color. A line might be blue while its equation remains black, particularly if you change the line’s formatting after adding the equation. VBA can read the line’s current RGB color and apply it to the label’s font. This is useful when a chart has several colored series and readers need to associate each equation with the correct line.

How the VBA color match works

The macro reads the color from the trendline’s line format, then assigns that color to the font of its data label. The important distinction is that the code copies a color value; it does not create a permanent link between the two formats. If you later recolor the line, run the macro again to update the label.

The example deliberately targets the first series and its first trendline. Excel charts can contain multiple series, and each series can have multiple trendlines, so choosing the correct objects matters. The macro also turns on the equation display. A trendline label is available when the equation or R-squared display is enabled.

VBA macro for the active chart

Select the chart you want to format before running this macro. It matches the equation label to the first trendline on the first series:

Sub MatchTrendlineLabelToLine()
    Dim ch As Chart
    Dim s As Series
    Dim tl As Trendline
    Dim lineColor As Long

    ' Require a selected chart.
    If ActiveChart Is Nothing Then
        MsgBox "Select a chart first."
        Exit Sub
    End If

    Set ch = ActiveChart

    ' Check that the chart contains a series and a trendline.
    If ch.SeriesCollection.Count = 0 Then
        MsgBox "The chart has no data series."
        Exit Sub
    End If

    Set s = ch.SeriesCollection(1)
    If s.Trendlines.Count = 0 Then
        MsgBox "The first series has no trendline."
        Exit Sub
    End If

    Set tl = s.Trendlines(1)
    tl.DisplayEquation = True

    ' Copy the trendline's current line color to its label font.
    lineColor = tl.Format.Line.ForeColor.RGB
    tl.DataLabel.Font.Color = lineColor
End Sub

The code uses Excel’s chart object model: SeriesCollection(1) means the first plotted series, and Trendlines(1) means the first trendline belonging to that series. It does not match every label in the chart. The Long variable stores the RGB color value, which is then assigned to the label’s font.

Run the macro in Excel for Windows

Save a copy of the workbook before running a macro. VBA actions cannot be undone with Ctrl+Z.
Save the working file as an Excel Macro-Enabled Workbook (.xlsm) if you need to keep the macro in that workbook.
Select the chart by clicking its chart area. Make sure the chart, rather than a worksheet cell or an individual line, is active.
Open the Visual Basic Editor from Developer > Visual Basic. If the Developer tab is not visible, enable it through File > Options > Customize Ribbon.
In the editor, choose Insert > Module, paste the code into the module, and return to Excel.
Run it from Developer > Macros, select MatchTrendlineLabelToLine, and choose Run.

If Excel blocks the macro, check the file’s security settings and whether the workbook is trusted. Do not enable macros in a file from an untrusted source just to change chart formatting. The code changes chart appearance, but macros can also perform other actions, so only run VBA you understand.

Targeting a different series or trendline

For a chart with several series, change the collection index in the code to the series and trendline you intend to format. For example, SeriesCollection(2) selects the second series, while Trendlines(2) selects that series’ second trendline. These indexes are positions in the chart’s collections, not necessarily worksheet column numbers or series names.

To format every trendline label in a chart, loop through the series and each series’ trendlines. This version reads each line color separately, so labels continue to correspond to their own lines:

Sub MatchAllTrendlineLabels()
    Dim ch As Chart
    Dim s As Series
    Dim tl As Trendline
    Dim lineColor As Long

    If ActiveChart Is Nothing Then
        MsgBox "Select a chart first."
        Exit Sub
    End If

    Set ch = ActiveChart

    ' Process every trendline in every chart series.
    For Each s In ch.SeriesCollection
        For Each tl In s.Trendlines
            tl.DisplayEquation = True
            lineColor = tl.Format.Line.ForeColor.RGB
            tl.DataLabel.Font.Color = lineColor
        Next tl
    Next s
End Sub

This loop displays the equation for each trendline it processes. If you want the R-squared value as well, set tl.DisplayRSquared = True in the loop. If you want to preserve the current display settings, remove the display-setting lines and run the code only for trendlines that already have a label.

Choosing RGB colors and label readability

Reading the trendline’s RGB value is usually preferable to typing a second color into the macro: the label then takes the actual color currently assigned to that line. It also avoids guessing at a theme color’s displayed RGB value. The label font color is set as a solid color, but the macro does not adjust font size, bolding, background fill, or transparency.

Matching colors can make a chart easier to scan, but a color match alone may not be enough for accessibility or printing. A pale yellow line and pale yellow equation can be difficult to read on a white background. Consider using a darker line color, a larger or bold label font, or a chart legend and clear series names as additional cues. If the trendline is dashed or thin, its label’s matching color still helps, but does not reproduce the line’s dash pattern.

When the trendline uses a color that is difficult to read, there is no requirement that the equation text match exactly. A deliberate contrast color may be clearer. In that case, assign a chosen color to tl.DataLabel.Font.Color instead of copying tl.Format.Line.ForeColor.RGB.

Common problems and fixes

No active chart: The macro checks ActiveChart. Click the chart area first, then run it again. Selecting a worksheet cell is not enough.
No trendline found: The example checks only the first series. If the trendline belongs to another series, change the series index or use the all-trendlines loop.
The equation appears unexpectedly: The sample sets DisplayEquation to True so it can format a label. Remove that line if you do not want the equation displayed; make sure a label already exists before formatting it.
The label color does not change after recoloring the line: The macro copies a snapshot of the line’s color. Run it again after changing the line, or use a separate macro whenever you update chart formatting.
Only the equation and R-squared text change together: They are formatted through the same trendline label font. This method does not assign separate colors to the equation and R-squared portions.
The line looks different from its label: Check whether the line uses transparency, a gradient, or other styling. The code copies its RGB foreground color; it does not copy all line effects or make the label a live style reference.

Excel for Mac and Excel for the web

Excel for Mac includes VBA support, and the same chart-object approach is generally applicable there. The Visual Basic Editor and macro-security controls may be reached differently than in Windows, and the Developer tab may need to be enabled. The Windows ribbon instructions above should not be treated as Mac menu directions. Save the workbook in a macro-enabled format if you want to retain VBA.

Excel for the web does not run VBA macros. To use this method, open the workbook in the desktop Excel application, run the macro there, and save the workbook. The resulting chart formatting can be viewed in the web app, but that does not make the VBA code runnable in the browser.

Frequently Asked Questions

Can Excel automatically match a trendline equation label to its line?
There is no automatic color link in the VBA approach described here. The macro copies the line’s current RGB color when it runs, so run it again after changing the line color.

Does this change the color of the trendline itself?
No. It reads the trendline color and changes the font color of the equation label. To change the line, format the trendline separately.

Can I match the R-squared label too?
Yes. The equation and R-squared value use the trendline’s label formatting, so setting DataLabel.Font.Color changes the displayed label text. Set DisplayRSquared to True if you also want the R-squared value shown.

Can I use a fixed color instead of the line color?
Yes. Assign an RGB value to the label font, such as tl.DataLabel.Font.Color = RGB(0, 112, 192). That sets a specific blue rather than reading the trendline’s current color.

Why does the macro format the wrong trendline?
The sample uses collection positions: first series, first trendline. Change those indexes to target another object, or use the loop when you want to process all trendlines in the active chart.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.