Dynamic Stacked column charting with VBA

[Bhupinder Rayat]

Hi All,

I would like to design a stacked column chart that stacks up supply based on
ranking, from cheapest to most expensive, and to keep the same colors on any
given day. the y axis will plot supply, and the x-axis will plot time (day
1, day 2, day 3 etc...)

E.g.

Day 1
Apples - supply 5, price 20p (color red on chart)
Pears - supply 3, price 25p (color blue on chart)
Bananas - supply 6, price 30p (color green on chart)

So on Day 1, the stack will be from bottom to top, Apples (red), Pears
(blue) and Bananas (green) (cheapest to most expensive)

Day 2
Pears - supply 4, price 15p (color blue on chart)
Bananas - supply 7, price 20p (color green on chart)
Apples - supply 3, price 25p (color red on chart)

On Day 2, the stack will be from bottom to top, Pears (blue), Bananas
(green) and Apples (red) (cheapest to most expensive).

I cannot see an easy way to do this. How do I apply a rank to every day and
make sure the colors stay the same. I haven't had much exposure to charting
with vba, is this possible to achieve?


Thanks,

B/