Custom bar colors for single series graph

[jtbleek]

I have an access report that gets exported to Excel. There is no changing how the data comes out of access. I want to be able to graph the data that spits out and have the different programs be different colored bars. The problem is as the data is formatted it makes it all one series. I have attached an example of what I want, but I had to manually change the colors of the bars and I would like to avoid that. Thanks for your help.

[Andy Pope]

Something like this,

Please Login or Register  to view this content.

You will need to adjust the range that contains the Program name.
Also the actual program names and the colour you want assigned.

[Andy Pope]

Update,

This version uses the Category labels directly from the series rather than a range reference to them.

Please Login or Register  to view this content.

[jtbleek]

Thanks Andy. I will report back with results later today.

[jtbleek]

Andy, were able to get this to in my posted example? i may be adding the code to the wrong place. Where should I add it? Thanks.

[Andy Pope]

Code goes in a standard code module.

[jtbleek]

Thank you for that example. My end goal for this is to allow another user to do this on there own. So basically if they could simply copy and paste this command button into the report they run it would be ideal. I attempted to, but got this error Run-time error '5': Invalid procedure call or argument on the following line

.SeriesCollection(1).Points(lngIndex).Format.Fill.ForeColor.RGB = colProgramColors(strPoint)

Can this command be copy and pasted to do this function on a period basis? Thanks.

[Andy Pope]

You would need to copy the button AND the routine.

Hard to tell why you get the error without seeing the code you used and the chart/data you used it on.

[jtbleek]

In the example you posted, if I create another cart using the same data as the first, select the new chart and press the button I get the error. Hopefully that helps. I believe it has to do what the series is named possibly. I'm not sure.

[Andy Pope]

No not really as I just created another chart based on the same data and it formatted the chart as expected.

You would need to post example file, including the chart you created.

[jtbleek]

I apologize Andy as I lead you astray with my first example which did not include the data also including the dates. In the example attached there is the chart you created, a smaller chart below it and to the left which does not include the dates in the series and in fact the button works great on that one. There is also a smaller one below and to the right of yours that includes the dates, which is what I would ideally like, that the button does not work and I assume it is because I change the criteria for the series. I'd like your code to work for if the dates are included, but do not know how to adjust it to do that. Thank you again for your help.

[Andy Pope]

The will ignore points where there is no time section to the category text.

Please Login or Register  to view this content.

[jtbleek]

Thank you that works perfectly. I know you mentioned for me to be able to copy it from spreadsheet to a different one I have to compy the button and the routine, but I am unclear on what you mean by that.

[Andy Pope]

You need to copy the code, or module.
You need to copy the button and Assign the macro to the new local version of the routine.