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

RandyBeckers

New Member
Joined
Dec 28, 2016
Messages
2
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
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

RandyBeckers

New Member
Joined
Dec 28, 2016
Messages
2
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 ...
 

Watch MrExcel Video

Forum statistics

Threads
1,122,355
Messages
5,595,678
Members
414,009
Latest member
SNesbyCarr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top