error in legendentry

[Mick Turner]

hello,

I have a chart that needs more legend entries than can be visible shown
in the chart.

When I try to access (= change color with Interior.Colorindex) the
legend entry, an error occurs (Unable to get the LegendKey property of
the LegendEntry class). Is this due to fact that the LegendEntry in
question is not "visible"?

If I decrease the number of LegendEntries, my macro will execute perfectly.

Here's my code:

Sub ColorMacro()
Dim minVoltage As Double
Dim maxVoltage As Double
Dim i As Integer

Charts("Voltage chart").Activate

minVoltage = ActiveChart.Axes(xlValue).MinimumScale
maxVoltage = ActiveChart.Axes(xlValue).MaximumScale

'changes the scale of the legend
With ActiveChart.Axes(xlValue)
.MinimumScale = 0
If maxVoltage <= 15 Then
.MaximumScale = 15
Else
.MaximumScale = maxVoltage
End If
.MajorUnit = 0.5
End With

'loop through legend entries
For i = 1 To ActiveChart.Legend.LegendEntries.Count
ActiveChart.Legend.LegendEntries(i).LegendKey.Select
If i = 1 Then
With Selection.Interior
.ColorIndex = 4
End With
End If
If i >= 2 And i <= 9 Then
With Selection.Interior
.ColorIndex = 6
End With
End If
If i >= 10 And i <= 20 Then
With Selection.Interior
.ColorIndex = 45
End With
End If
If i > 20 Then
With Selection.Interior
.ColorIndex = 3
End With
End If
Next
End Sub

Note that my code is not finished yet. What I'm trying to do is to
change the legend maximum and minimum values (to 0 and 15, or if voltage
is higher, then that value) and change the colours so that the first
legendkey (0-0.5) has one colour, a couple of next legendkeys has
another and so on.

I would also like to delete some of the legend entries so that only one
is shown per colour. And moreover, if possible, I would like to change
the texts of the legend entries manually through VBA, but I guess that
is not possible.

I would appreciate any help!

Thanks in advance,
Mike