Creating full-spectrum color gradients in bar chart based on input value

You can apply a gradient fill the bar. In the Format dialog you can add Graident stops and set the colours

You would need to format each data point (bar) based on it's value in relation to a colour spectrum.

The usual way to have conditional bar colours is to use multiple series with formula to control which series has the data value. Given a full colour spectrum vba would be easier

you can use macro recorder to get setting the colour of a data point.

The big question is how do you know what colour to use for any given data value?

It would scale from dark red at 1 through orange > yellow > green > blue > to bright cyan after 200. I can mess with that in practice once I'm able to use a macro to set a color for each value. Any advice on setting that up? So far I have this VBA module but it seems overly complicated and doesn't seem to work:

- VBA Sheet:

Please Login or Register  to view this content.

- Module:

Please Login or Register  to view this content.

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however you need to include code tags around your code.

Please take a moment to add the tags. Posting code between tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

Next time don't forget the code tags.


Try this code

Please Login or Register  to view this content.

In the example you will need to update the RGB values to suit your spectrum

You would need to change the value in B2:B7 in order for the code to reformat the columns.

If the values are formula then you will need to use the calculate event

As you have not posted an example file I can only go on the code.

The bar length is determined by values in B2:B7, the colour of the bars is determined by content of C2:C7, which you call rank_rng. If the values in C2:C7 are not somehow based on the values of B2:B7 then they will not change, therefore the colour for each point will not change.