Problem Setting BackColor.RGB dynamically for a shape gradient - value is out of range

[High Plains Grifter]

Hi peeps,

I am using a row of autoshapes to show a key underneath a macro-cotrolled map made of other shapes, whose colour depends on various values. There is column of data for the key with ten rows; one for each shape in the key. This column of data holds RGB values for the shape colours, in this case between 255 (red) and 62975 (yellow). I am trying to fill each shape in the key with a gradient, so that the key appears as one long continuous gradient showing the colour range for the map (using one long shape for the key is not practical I think given the complexity of some of the gradients).

Here is the contents of the range "KeyColours"
[TABLE="width: 48"]
<TBODY>[TR]
[TD="class: xl66, width: 64, bgcolor: #d9d9d9, align: right"]62975
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]56575
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #d9d9d9, align: right"]48895
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]41215
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #d9d9d9, align: right"]33535
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]25855
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #d9d9d9, align: right"]18175
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]10495
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #d9d9d9, align: right"]2815
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #d9d9d9, align: right"]255
[/TD]
[/TR]
</TBODY>[/TABLE]

And here is the code:

Sub Key()
    Dim i As Integer
    Dim strName As String
    Dim rLeftColour As Range
    Dim shp As Shape
For i = 1 To 10
        strName = Choose(i, "K_X01", "K_X02", "K_X03", "K_X04", "K_X05", "K_X06", "K_X07", "K_X08", "K_X09", "K_X10")
        Set shp = Sheets("Choropleth Map").Shapes(strName)
        Set rLeftColour = Range("KeyColours").Cells(i, 1)
    With shp
            .Fill.Visible = msoTrue
            .Fill.ForeColor.RGB = rLeftColour.Value
        If i < 10 Then
                .Fill.BackColor.RGB = rLeftColour.Offset(1, 0).Value
        Else
                .Fill.BackColor.RGB = rLeftColour.Value
        End If
            .Fill.TwoColorGradient msoGradientVertical, 1
    End With
Next
End Sub

When I F8 through the code, the values all fill as follows, with i = 1
rLeftColour.Value = 62975 [as expected]
.Fill.Forecolor.RGB = 62975 [as expected]
rLeftColour.Offset(1,0).Value = 56575 [as expected]
.Fill.BackColor.RGB = 16777215 [NOT EXPECTED!]

When I get to the line mentioning the TwoColorGradient, the code fails with this message:

"
Run-time error '-2147024809 (80070057)':
The specified value is out of range
"

Any ideas why I get such an unexpected value for the background colour (I guess this is what is causing the error)? What can I do to fix things?

Thanks for your time and any help!

 

[High Plains Grifter]

I have solved my own problem - I neglected to add the all-important line ".Fill.Transparency = 0" after "With shp". Not sure why that has such a massive effect on the background colour and none on the foreground colour, but there we go - it works.

Next time I may write down the problem in notepad before I put it on here - it seems that the process of fully describing the problem helps cosiderably in solving it. If anyone reads this and knows why this effected only the background colour, I would be interested to know the answer, but this can now be considered low priority