Excel 2007 chart color change based on the label.

[frogman7]

I have 12 charts 1 for each person and the data changes every week and
some of the values have 0 so are filtered out. On the chart I want
0-15 to be blue, 16-30 to be light purple, 31-60 to be a cream color,
61-90 to be light blue, and 90+ to be dark purple. With the code
below I can change the color if I take the if statement out but I
would like to change the color based on the label. I have tried
several things like .text, .name, and .label. I just can't fine the
right name.
Name1
0-15 1
61-90 1
90+ 3

Name 2
0-15 14
16-30 5
31-60 1
61-90 6
90+ 12




For i = 1 To ActiveChart.Legend.LegendEntries.Count
ActiveChart.Legend.LegendEntries(i).LegendKey.Select
If ActiveChart.Legend.LegendEntries(i).Label.Text = "0-15"
Then
With Selection.Interior
ActiveChart.Legend.LegendEntries(i).LegendKey
.ColorIndex = 60
End With
End If
If ActiveChart.Legend.LegendEntries(i).Name = "16-30" Then
With Selection.Interior
.ColorIndex = 2
End With
End If
If ActiveChart.Legend.LegendEntries(i).Name = "31-60" Then
With Selection.Interior
.ColorIndex = 3
End With
End If
If ActiveChart.Legend.LegendEntries(i).Name = "61-90" Then
With Selection.Interior
.ColorIndex = 4
End With
End If
If ActiveChart.Legend.LegendEntries(i).Name = "90+" Then
With Selection.Interior
.ColorIndex = 5
End With
End If
Next i

 

[frogman7]

I have 12 charts 1 for each person and the data changes every week and
some of the values have 0 so are filtered out.  On the chart I want
0-15 to be blue, 16-30 to be light purple, 31-60 to be a cream color,
61-90 to be light blue, and 90+ to be dark purple.  With the code
below I can change the color if I take the if statement out but I
would like to change the color based on the label.  I have tried
several things like .text, .name, and .label.  I just can't fine the
right name.
        Name1
0-15    1
61-90   1
90+     3

                Name 2
0-15    14
16-30   5
31-60   1
61-90   6
90+     12

For i = 1 To ActiveChart.Legend.LegendEntries.Count
         ActiveChart.Legend.LegendEntries(i).LegendKey.Select
         If ActiveChart.Legend.LegendEntries(i).Label.Text = "0-15"
Then
             With Selection.Interior
             ActiveChart.Legend.LegendEntries(i).LegendKey
                 .ColorIndex = 60
             End With
         End If
         If ActiveChart.Legend.LegendEntries(i).Name = "16-30"Then
             With Selection.Interior
                 .ColorIndex = 2
             End With
         End If
         If ActiveChart.Legend.LegendEntries(i).Name = "31-60"Then
             With Selection.Interior
                 .ColorIndex = 3
             End With
         End If
         If ActiveChart.Legend.LegendEntries(i).Name = "61-90"Then
             With Selection.Interior
                 .ColorIndex = 4
             End With
         End If
         If ActiveChart.Legend.LegendEntries(i).Name = "90+" Then
             With Selection.Interior
                 .ColorIndex = 5
             End With
         End If
     Next i

Is there any help on this. Even if someone says that it can't be done
at least that will let me know to move on. I have the code to just go
through and assign colort to each legend entry but when I filter out
the 0 values then the colors change based on position. the 3rd legend
entry is changed to color 19.

Dim i As Integer
Dim j As Integer

'loop through all the charts then through all the legends to
change color
Application.Sheets("Individual Aging By Tech").Activate

For j = 1 To ActiveSheet.ChartObjects.Count
ActiveSheet.ChartObjects(j).Activate

'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 = 17
End With
End If
If i = "2" Then
With Selection.Interior
.ColorIndex = 18
End With
End If
If i = "3" Then
With Selection.Interior
.ColorIndex = 19
End With
End If
If i = "4" Then
With Selection.Interior
.ColorIndex = 20
End With
End If
If i = "5" Then
With Selection.Interior
.ColorIndex = 21
End With
End If
Next i
Next j

End Sub