How to color coordinate series in a bar chart that has 2 columns on the x-axis

[Rhino_dance]

p65_mrexcel_graph_question_sept10.JPG

I've created the above bar chart which has two columns on the x-axis. These columns are 'MONTH & 'LOCATION'. Now I want all common months in the chart to be the same color. e.g. all 'JAN' green, all 'FEB' blue, all 'MAR' Red, etc...

How do I do this?

One attempt I made was to highlight the first data bar. (This results in every other bar being selected, which I'm not sure I understand.) Nonetheless, I then right click and choose 'Format Data Series'. Then I choose 'Fill' and then I choose 'Automatic' and also check 'vary colors by point'. This places multiple colors on the bars, however, the same months don't exhibit the same color. Any help would be appreciated.

(Also, not sure why my image is only showing as a link, since I chose ''Insert Image' when embedding it in this post.)

[NickyC]

Hi
I'm assuming that your values are all in one long series, rather than a separate series for each city.
If so, this macro should work:

Please Login or Register  to view this content.

[Rhino_dance]

p65_mrexcel_graph_question_sept11_data_table.JPG

Thanks for the response. The above link shows the data table I'm working with. The Location and Months are in separate columns. So, yes, each city would have its own series.

The above macro worked- my only complaint being that the colors (ok 'Colours'...) are overly bright. How do I replace them with more subdued hues? appreciate your help.

[NickyC]

Hi
The colours (yep, I'm, an Aussie!) are from the scheme colours in your workbook. To change them you can either change your workbook's scheme colours, or change the scheme colour numbers in this array:

Please Login or Register  to view this content.

[Rhino_dance]

Thanks very much Nicky. I appreciate all your help

[Rhino_dance]

Nicky, I've been experimenting with generating different colors in the graph per your VBA code. I'm using the first table on following web page as a guide for EXCEL colors by number (RED=3;Green=4, etc..)

http://dmcritchie.mvps.org/excel/colors.htm

I changed the numbers in your vba code to generate mellower colors. Entering this:
ColourZ = Array(9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20)

However, I don't seem to be getting the corresponding colors. Here's the chart that's created when I enter the above numbers:

p65_mrexcel_graph_question_sept13.JPG

It looks like it's still generating colors 2,3,4, etc... What am I missing? Thanks

[NickyC]

interesting - when I use your array I get different colous (still pretty gaudy, though)
the trouble is that for simplicity I used scheme colours, which tend to be fairly basic
try running with:

Please Login or Register  to view this content.

alternatively, pick or add a blank sheet and run this macro
it will add 80 shapes to the sheet, filled with each of the 80 scheme colours, and with the scheme colour number as text
you can then pick the numbers of the scheme colours you want, and write them into the macro

Please Login or Register  to view this content.

this second macro then deletes all shapes on the sheet:

Please Login or Register  to view this content.

alternatively, if you want to pick 12 RGB numbers we can modify the macro to use those instead

[Rhino_dance]

Nicky, thanks again very much. I'm learning a lot from you. I tried your new series of color codes and the results were closer to what I wanted. I also selected specific colors codes myself and generate another custom graph with pretty good results.

What I'd REALLY like to do is exactly match the colors that are generated by the 'Office' color scheme. (This is the default color scheme used when I create a simple bar chart with only one series on the x-axis.) But I don't see those exact colors in the 80 colors generated by your schemecolours_show() function. Any idea how I would generate the 'Office' scheme colours in a graph?

Cheers

[NickyC]

ok, try this version, that uses RGB colours derived from the automatic chart settings, not the inbuilt schemecolors

you can put whatever RGB values you like into the array

Please Login or Register  to view this content.

[Rhino_dance]

Thanks very much Nicky- that 's worked great! A good study for me in how to apply vba to charts.

[Cutter]

@ Rhino_dance

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.