Bar Chart Colors based off of value

[wyeah]

Excel 2010 Windows 7

Is there VBA code that will change the color of the bars in a bar chart based off of their value? My chart is "Chart 14" and if the value for a bar, there are 14 bars, is less than 100 I would like it to be red and greater than 100 to be green.

Thanks,
Alan

 

[pgc01]

Hi Alan

If the values will never change I'd change the coulours manually. With just 14 bars it would be a matter of seconds.

If the values will change and you want the colours to follow, use a stacked bar chart. Split the values in 2 series, one for the less than 100 and another for the others.

 

[wyeah]

The values change each time the spreadsheet is updated. I will try the "Stacked Bar" and see what I can figure out.
Thanks for the info

 

[VoG]

This may help you as well Conditional Formatting of Excel Charts - Peltier Tech Blog

 

[Osric Wuscfrea]

So I have a stacked bar chart which generates a Gannt which works fine.

Using the Peltier method to separate out the values, in this instance I am trying to sort by RAG (Red, Amber, Green etc) status instead of a number which works just fine.

However I cannot seem to select ALL the data in the new table, I can select a single column only in the new one, not all. If I select all the chart breaks. The desired end state is to select the RAG and that makes the bar the matching colour. I have no idea why I can't select more data, something I do regularly in other documents.

To be clear on the intent: the Gannt works fine, just pump in two dates, chart does the rest. What I want to do is select the RAG and then the corresponding bar matches the colour.

The spreadsheet is below and I would appreciate a steer where this is going wrong.

http://www.dead-fish.com/Files/Book1.xlsx

 

[Osric Wuscfrea]

Anyone?