asked on

Excel 2007 VBA: Applying a gradient to a Series Collection Trendline

How do I modify this code to produce a gradient in the trendline?

Sub HideShowTrendline()
Dim trig As Range
Set trig = ActiveSheet.Shapes("Chart 1").TopLeftCell.Offset(2, 2)
If trig = 1 Then
   ActiveSheet.ChartObjects("Chart 1").Activate
   ActiveChart.SeriesCollection(5).Trendlines(1).Delete
   trig = ""
Else
   ActiveSheet.ChartObjects("Chart 1").Activate
   ActiveChart.SeriesCollection(5).Select
   ActiveChart.SeriesCollection(5).Trendlines.Add
   ActiveChart.SeriesCollection(5).Trendlines(1).Select
      With ActiveChart.SeriesCollection(5).Trendlines(1)
         .Border.Weight = xlThick
         .Border.LineStyle = xlContinuous
         .Border.ColorIndex = 17
         .Border.Weight = xlThick
         .Border.LineStyle = xlContinuous
         .Format.Shadow.Type = msoShadow22
      End With
   trig = 1
End If
[A4].Select
End Sub

Open in new window

Thanks,
John

andrewssd3

What do you mean? Do you want to display the gradient on the trendline? You can display a formula which might do what you ask, but maybe you want to change the gradient? Please be specific.

John Carney

ASKER

Change the "fill" of the trendline to a gradient. It's too bad they removed the ability to record shape formats in 2007, or I wouldn't even need to ask this question! :- )

andrewssd3

Sorry - I automatically thought of the line gradient in the context of a chart! I'm sorry I can't work out how to do this. You can get the trendline, and I would expect to be able to do something like this:

    Dim t As Trendline
    
    Set t = ActiveChart.SeriesCollection(1).Trendlines(1)
    
    With t.Format.Fill
        .TwoColorGradient msoGradientFromCenter, 2
    End With

Open in new window

but the Format.Fill does not work on a trendline, and I can't work out an alternative. Maybe you just can't do it except through the user interface - it never records, even in Excel 2013.

John Carney

ASKER

Thanks, andrewssd3. I'll leave this question open for a while just in case someone knows how to do it. I guess it's not the end of the world if I can't accomplish it!

ASKER CERTIFIED SOLUTION

andrewssd3

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

John Carney

ASKER

Thanks!