Dynamic Gradient Background

[broknspyrl]

Hi guys,
I hope you will be able to help, i have been tasked with trying to create a line graph which shows the progression of a metric over the course of a number of quarters. This needs to then be plotted on a chart with a gradient background (Red to Amber to Green (From bottom to top)) which shows the RAG status of the points.

The problem i am trying to wrestle with is how to dynamically change the gradient based on the values in the data table (three helper columns). The table will store the value of the metric, the upper red, amber and green thresholds. Obviously the upper green will be the highest value of the data table. I do not mind whether this can be accomplished through a (number of) cell function(s) or through VBA macro.

I have searched this forum and not found anything, i have also looked on Google and found a few tutorials on how to dynamically colour cells, but nothing on chart backgrounds.

I hope someone will be able to help!

Thank you very much in advance

p.s. for a sample of the layout and chart please see the attachment which will hopefully make my explanation clearer.

Using MS Excel 2010

[Andy Pope]

You can use 3 series plotted as cluster column.

Change overlap and gap of series.
Note also the over of values has changedso smaller columns are infront of taller columns

[broknspyrl]

Thank you very much for this although I'm not sure i completely understand what/how you have done this. I have tried to recreate the chart and can't, the series seem to be cumulative i.e. the bars go up to the sum of the thresholds.

Secondly, the vertical axis does not seem to change when the values change, e.g. if i update the highest value to 15, the axis stays at 10.

[Andy Pope]

See this version where the overlap is not 100. Use the cluster column chart and not the stacked column.

If you don't fix the vertical axis scale to have a maximum then you will get a gap at the top of the scale.