Cell values translated to color gradients: macro needed

Dear Forum,
Hello. I am trying to construct a visual indication of loudness across time. Because I am stuck in excel 2003, I have to figure out how to manipulate colors to the extent that I’d like with VBA. If you look at my attached workbook, you’ll see data, a color legend, and two charts, which are identical except for the series' fills. How to get from “Raw Product” to “End Product” with a macro, by specifying color gradients as values in cells (C3:D45 - start color/end color)? In addition, a preceding IF condition will check for an “R” (in which case there will be no area fill at all).

So far, I have a macro that can change the series into a solid color given cells' values, and I have a macro that can give a series a predetermined gradient already written into the code. I need a macro that will do both at the same time, though.

Any ideas? Thanks.

Originally Posted by JBeaucaire

Your macros appear to be missing from the workbook. To merge them, we would need to see them.

Hi JBeaucaire,
Thank you for your response. I apologize for not getting back to you sooner, but am traveling and have limited internet access. Okay, I solved my problem, as I mentioned above. The solution is attached as "Book 1", and here is the code:

Private Sub CommandButton1_Click()

Dim i As Integer
Dim clr As Double
For i = 1 To 43
ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(i).Select
    ActiveChart.SeriesCollection(i).Points(1).Select
        With Selection.Border
        .Weight = xlThin
        .LineStyle = xlNone
    End With
    Selection.Shadow = False
    Selection.InvertIfNegative = False
    If ActiveSheet.Range(Cells(i, 3), Cells(i, 3)).Value = "r" Then
    Selection.Interior.ColorIndex = xlNone
    Else
    Selection.Fill.TwoColorGradient Style:=msoGradientVertical, Variant:=1
    With Selection
        .Fill.Visible = True
        .Fill.ForeColor.SchemeColor = ActiveSheet.Range(Cells(i, 3), Cells(i, 3)).Value
        .Fill.BackColor.SchemeColor = ActiveSheet.Range(Cells(i, 4), Cells(i, 4)).Value
    End With
    End If
    Next i
End Sub

Now, regarding this code, it is set for 43 series on the graph. However, I intend to turn the spreadsheet into autoexpanding chart, such as in the attached spreadsheet "Autoexpanding chart". In other words, there will be an indeterminate number of series on the graph. Any idea how to solve this problem? Thanks for your help and interest.