Stacked Chart - Change Bar Colors Based on Date (X-Axis)

Hi all;

I am wanting the colors of the bars in a stacked chart to change based on the date in the X Axis. As the current date arrives, the stacked bar needs to change to various blue shades. If the date is in the future, the stacked bar needs to be various grey shades. Can anyone suggest how this can be done? If it requires VBA could you please also include a snippet example? I have an image of what I am trying to achieve attached.

Thanks so much for sharing your expertise!

JL

Hi JL,

Attached is a working example of what I think you're after.
The numbers are random so it doesn't quite look like the one in your example pic, but it should give you the idea of how to accomplish it.

Basically column A is your X-Axis labels, columns B&C are the main data. Columns E&F and G&H take that main data and using simple formulas decide which columns to put the numbers in. So if the date is in the past the numbers go into E&F, if the date is in the future the numbers go into G&H.

The graph is then plotted from columns E to H which allows you to allocate different colour sets to past and future data.

There are probably easier/better ways of doing this, but this method works for me so it's the one I use.

Hopefully that all makes sense. Happy to help further if not.

BSB.

Originally Posted by etaf

you would probably need to setup the series using a range of IF statements - so if the date changes then the range series changes and so the colour

see example attached

change the date in cell A2 and then copy down and you will see the colours change based on todays date

Its beautifully done! How do you get the colour to change? it's not a macro!

figured it out: check this link: http://peltiertech.com/conditional-f...-excel-charts/

thanks