Chart Macro Question

[WBS]

Tryed to use this code to draw a trendline to same color as the chart series but it doesn't seem to be working--the trendline is always black regardless of the series color. Any suggestions? Thanks.

Sub DrawTrendLine()
Dim TrendLineColor As Integer
TrendLineColor = ActiveChart.SeriesCollection(1).Interior.ColorIndex
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).Trendlines.Add(Type:=xlLinear, Forward:=0, _
Backward:=0, DisplayEquation:=False, DisplayRSquared:=False).Select
ActiveChart.SeriesCollection(1).Trendlines(1).Select
With Selection.Border
.ColorIndex = TrendLineColor
.Weight = xlMedium
.LineStyle = xlContinuous
End With
End Sub

 

[pgc01]

Hi WBS

As you know ColorIndex only allows you to choose from 56 colours. That's why even default columns charts use Color instead, allowing for RGC colour specification.

Please try:

Sub AddTrendline()

With ActiveChart.SeriesCollection(1)
    .Trendlines.Add.Border.Color = .Interior.Color
End With
End Sub

Hope this helps
PGC

 

[WBS]

Thanks for your suggestion. It works great!