How to format trendline as same color as series line in XL2007
 

I am using Excel 2007. I want to change the trendline color (and type/width)
to match the color of it's parent data series line color. i have this vb
code which works well provided i manually set the color of data line and
trendline first. if the color is set by Automatic color assignment (which is
default) then the code does not work and color doesn't change (but linetype
and width changes). I am not sure
if this is a bug or something i am not doing properly...

Sub change_trendlines()
Dim mysrcolor, myset As ColorFormat
Dim ch1 As ChartObject
Dim series1 As Series
Dim trend1 As Trendline
Dim myline As LineFormat


For Each ch1 In ActiveSheet.ChartObjects
ch1.Activate
For Each series1 In ActiveChart.SeriesCollection
Set mysrcolor = series1.Format.Line.ForeColor
For Each trend1 In series1.Trendlines
Set myline = trend1.Format.Line
myline.DashStyle = msoLineDash
myline.Weight = 2
myline.ForeColor.RGB = mysrcolor.RGB
Next trend1
Next series1
ch1.Activate
Next ch1

End Sub

any help on this is highly appreciated