Solved: Bar Chart code, help please

[Spratt_Cary]

Running xl 2000. I need help with code. The bar chart is coming from a pivot table. Upper management wants to see the bars in different colors according to a scale.
0-5 Red
6 - 15 Bronze
16 - 35 Silver
36 Gold
Right now we do each bar one by one. Ugly.
There are 116 bars. I can change the bars using code:
ActiveChart.SeriesCollection(1).Points(1).Select
Selection.Fill.PresetTextured PresetTexture:=msoTextureWhiteMarble. ' Silver

ActiveChart.SeriesCollection(1).Points(2).Select
Selection.Fill.PresetTextured PresetTexture:=msoTextureWhiteMarble.


But I am dumbfounded on how to even start on the scale values. I am attaching the spreadsheet. I had to delete, hide some information. I hope this is enough information.

Thanks in advance
Brenda:hi:

[Kieran]

Does this http://peltiertech.com/Excel/Charts/format.html help?

[Spratt_Cary]

Thanks Kieran,

Your suggested web site is great and a lot of valuable information. It did not cover information regarding pivot reports/charts, but I was able to redo my raw data and use information gained from your suggested web site. I have saved this site as a favorite and will be using it frequently. To all of you that work with charts, please take the time to review this site, listed above by Kieran.

thanks again
Brenda

[JonPeltier]

There is a specific page on that site which discusses conditional formatting of a chart, that is, applying different formats based on values.

http://peltiertech.com/Excel/Charts/ConditionalChart1.html

The trick is to have multiple series in the chart, each one with one of your specified formats. Formulas in the cells dictate which series has a point at each category.

P.S. Thanks for your nice words about my site, and thanks Kieran for posting the link.

[Spratt_Cary]

Thanks Jon for a wonderful site, your information posted above is exactly what I used. I originally had a pivot report but I could not get the information to format correctly, so I created another raw data sheet according to your instructions and it works...great
Thanks again
Brenda