A Real Challenge (Chart formatting) for charity!

Hello all!

If you like a challenge, this post is for you! I am a new member and can't wait to hear what you guys propose to help me with my problem! I will try to be as clear as possible. Here it is....

The task: Make a tower that illustrates the composition of our current pool of donations. Donations are made in rounds so each level of the tower is one round. Also make it so the user can plug in numbers and the chart adjusts accordingly. Easy enough! Just make a 100% stacked column! Not so fast... The original donor (the company) tends to keep donating throughout the many rounds of donations and makes up a certain percentage of each round after round 1. I would like to illustrate this percentage in each respective level. EXAMPLE: In a 100% stacked column chart, the Company donates $100 in the opening round. Of the second round, which happens to be $200, the company is resposible for 50%. The chart would then show a first level of one color (red) and a second level (twice as thick) with the right half red and left half a different color (green) that would represent the other donations.

The problem: I initially thought the original company would only make up the rest of the rounds in incrememnts of 10%. I make the chart I wanted by making a 100% stacked column with 10 columns, then adjusting the format of the columns so there is no space between them. This makes for the appearance of one big tower. I then went ahead and changed the formats individualls so the series that came up when I plugged in 10% had one column that was red and the rest green, 20% and I would reformat 2 of the sections red etc.... Little did I know, the company sometimes makes up a percentage that can be as specific as 22.5%! No more 10 columns. It doesn't get more specific than .5%, so I now am stuck with a 100% stacked column chart with 200 column and up to 7 levels. This means I would have to individually format almost 9000 pieces of this chart!!!

My (semi)solution: I already know that there is no way to format multiple data series in a chart. All I could really think of to save some time was to make a macro that filled a data series, but even with this i had to press the same key hundreds of times. Is there a way to create a macro that would re-do this initial macro over and over like 20 times? This would at least save me some time as I could do 10% at a time as opposed to .5%.

Here is the code from my macro. All it does is change the fill of a selected data series.

Please Login or Register  to view this content.

Any and all help is appreciated!!!! Thank you in advance! It is for a great cause!

can you post workbook example, having hard time visualising what you have created.