Excel VBA - automatically change colors of more than 25 legend entries in contour plot

[RandyBeckers]

I made an excel template with some VBA behind to make a contour plot of some hardness data (x, y and hardness data provided in .csv file).

The user can browse to the csv file with raw data, then data is processed and placed in a pivotTable. From this table a contour plot is made. In the main tab the user can choose the scale in the graph, set minimum and maximum scale ...

Depending on the amount of legend entries, a color list is made (with conditional formatting: form blue over cyan to green for the first half of the values, green over yellow to red for the second half of the values).

The interior colors of these conditional formatted cells are used to change the legend entries colors with a loop (from 1 to legendentries.count). This works very well for less than 25 legend entries. If more legend entries are present, the reddisch colors aren't adjusted and some naughty excel colors are used instead. Does somebody have an idea why more legend entries can't be filled in?

code:

Set hardnessmap = Sheets("hardnessmap").ChartObjects("Hardnessmap").Chart

For i = 1 To .LegendEntries.Count
color = ColorRange.Range("A1").Offset(i - 1, 0).DisplayFormat.Interior.color
hardnessmap.LegendEntries(i).LegendKey.Interior.color = color
Next i

 

[RandyBeckers]

I just found out I can't even change the color of the legend entrie on the 'normal way': when right-clicking the legend entrie - format band -fill is set to automatic and I can't change it to solid fill ...