Changing the Gradient Fill of an Area Graph - VBA

[greekmac7]

Hi everyone -

I have a macro that changes the colour of area charts automatically, according to the colour of a cell in the data series. (where the trend is going up, they are red, and when they are going down, they are green).

The macro works great, but I really want my area graphs to have a gradient fill as this is what they currently have when I update them manually.
When I try to add the gradient fill I want in the "source cell", my graph turns black. When I try to add a gradient in a separate macro using the macro recorder, I lose my "source cell".

Here is my code:

Sub refreshchart()
'
' refreshchart Macro
'
Dim oChart As ChartObject
Dim MySeries As Series
Dim FormulaSplit As Variant
Dim SourceRange As Range
Dim SourceRangeColor As Long


'Loop through all charts in the active sheet

For Each oChart In ActiveSheet.ChartObjects

    'Loop through all series in the target chart

   For Each MySeries In oChart.Chart.SeriesCollection


        'Get Source Data Range for the target series

       FormulaSplit = Split(MySeries.Formula, ",")

        'Capture the first cell in the source range then trap the color

       Set SourceRange = Range(FormulaSplit(2)).Item(1)

        SourceRangeColor = SourceRange.DisplayFormat.Interior.Color


        On Error Resume Next

        'Coloring for Excel 2003

       MySeries.Interior.Color = SourceRangeColor

        MySeries.Border.Color = SourceRangeColor

        MySeries.MarkerBackgroundColorIndex = SourceRangeColor

        MySeries.MarkerForegroundColorIndex = SourceRangeColor


        'Coloring for Excel 2007 and 2010

       MySeries.MarkerBackgroundColor = SourceRangeColor

        MySeries.MarkerForegroundColor = SourceRangeColor

        MySeries.Format.Line.ForeColor.RGB = RGB(234, 234, 238)

        MySeries.Format.Line.BackColor.RGB = RGB(234, 234, 238)

        MySeries.Format.Fill.ForeColor.RGB = SourceRangeColor

    Next MySeries

Next oChart

End Sub

Is there any way of adding a gradient, regardless of the foreground background colour?

Many thanks in advance.

Ed.