Auto change chart column colors

[jmattioli]

Hey all, I am stumped with this, and I haven't used VBA at all. I have 2 columns of data that are charted in a 3D clustered column chart, which I have made "dynamic" to only chart the most recent 12 records. My question is, is there a way to automatically change the color of the columns in the chart based on the data i have recorded? Specifically, if there has been two consecutive increases, the column needs to change to red.

Example:
Date: ValA ValB
1/1 5 4
2/1 7 3
3/1 8 3
4/1 2 6
5/1 5 7

The data point in ValA for 3/1 would automatically change to red because it has increased two times in a row. Likewise, the data point in ValB for 5/1 would also be red, because of increasing two times in a row.

Any ideas? Thanks

 

[p45cal]

Could you post a sample workbook on the interweb, say box.net or some such?

 

[jmattioli]

Could you post a sample workbook on the interweb, say box.net or some such?

Not really sure how to do that. Never tried it before, but if you wouldn't mind letting me know how, I'll give it a shot

 

[p45cal]

go to www.box.net, register (free) follow instructions there.

 

[jmattioli]

go to www.box.net, register (free) follow instructions there.

maybe this will work. make sure to download to see the original
http://www.box.net/shared/24aqcxqvyberfbcejrup

 

[p45cal]

This can be done with or without macros.
Without macros will involve adding series to the charts which overlie existing series. You would set up extra columns for these series. An example for column B values in column F (F4 formula is copied down):

Excel Workbook
	A	B	C	D	E	F
1		>14	>30	Len		>14 rising
2	01-Apr	97	32	12		#N/A
3	08-Apr	77	37			#N/A
4	15-Apr	12	15			#N/A
5	22-Apr	11	12			#N/A
6	29-Apr	14	11			#N/A
7	06-May	15	7			15
8	13-May	11	5			#N/A
9	20-May	16	11			#N/A
10	27-May	15	8			#N/A
11	03-Jun	17	11			#N/A
12	10-Jun	22	16			22
13	17-Jun	21	14			#N/A
14	24-Jun	31	13			#N/A
15	01-Jul	29	12			#N/A
Sheet

I chose #N/A to appear in cells because excel doesn't attempt to chart them. But because you've used clustered charts it gets very fiddly to have the bars overlie each other (I haven't tried very hard).
John Peltier explores this technique here: http://peltiertech.com/Excel/Charts/ConditionalChart1.html and there's a video here.

I think using VBA (macros) might be better, in the file I've posted here at box.net, I've written a small macro (adapted from something I found on the interweb and possibly ascribable to Andy Pope, since he's pretty good on this sort of stuff - see his pages here) which runs through all the charts on the active sheet (there's only one at the moment) and changes the fill colour for the first series bars (only the first series at the moment) to red if a bar is the 3rd in a series of 3 rising bars. This macro runs on the press of the added button on that sheet. So experiment by changing the values in column B and pressing the button (macros will need to have been enabled).

You/we still need to develop this as I'm sure you'll want to do this on multiple series within (probably) multiple charts, even on multiple sheets.

You might want to change how you highlight the bars, making multiple series red may make it difficult to identify just which series a given highlighted bar is a member of. Perhaps change the transparency, or the border thickness or colour, or…

Of course, later you'll be able to dispense with the button and have the macro triggered automatically.

Is this vba solution one you'd be happy to proceed with?

 

[jmattioli]

This can be done with or without macros.
Without macros will involve adding series to the charts which overlie existing series. You would set up extra columns for these series. An example for column B values in column F (F4 formula is copied down):

Excel Workbook
	A	B	C	D	E	F
1		>14	>30	Len		>14 rising
2	01-Apr	97	32	12		#N/A
3	08-Apr	77	37			#N/A
4	15-Apr	12	15			#N/A
5	22-Apr	11	12			#N/A
6	29-Apr	14	11			#N/A
7	06-May	15	7			15
8	13-May	11	5			#N/A
9	20-May	16	11			#N/A
10	27-May	15	8			#N/A
11	03-Jun	17	11			#N/A
12	10-Jun	22	16			22
13	17-Jun	21	14			#N/A
14	24-Jun	31	13			#N/A
15	01-Jul	29	12			#N/A
Sheet

I chose #N/A to appear in cells because excel doesn't attempt to chart them. But because you've used clustered charts it gets very fiddly to have the bars overlie each other (I haven't tried very hard).
John Peltier explores this technique here: http://peltiertech.com/Excel/Charts/ConditionalChart1.html and there's a video here.

I think using VBA (macros) might be better, in the file I've posted here at box.net, I've written a small macro (adapted from something I found on the interweb and possibly ascribable to Andy Pope, since he's pretty good on this sort of stuff - see his pages here) which runs through all the charts on the active sheet (there's only one at the moment) and changes the fill colour for the first series bars (only the first series at the moment) to red if a bar is the 3rd in a series of 3 rising bars. This macro runs on the press of the added button on that sheet. So experiment by changing the values in column B and pressing the button (macros will need to have been enabled).

You/we still need to develop this as I'm sure you'll want to do this on multiple series within (probably) multiple charts, even on multiple sheets.

You might want to change how you highlight the bars, making multiple series red may make it difficult to identify just which series a given highlighted bar is a member of. Perhaps change the transparency, or the border thickness or colour, or…

Of course, later you'll be able to dispense with the button and have the macro triggered automatically.

Is this vba solution one you'd be happy to proceed with?

Thanks. I am going to try and play around with this today. In the meantime, do you know how to make the width of the columns wider instead of having the series being so close to each other?

 

[p45cal]

do you know how to make the width of the columns wider instead of having the series being so close to each other?

I'd guess this is because the x-axis is a date and there is room left for 6 days between plotted data. Try changing the x-axis format to type Text.

 

[jmattioli]

I'd guess this is because the x-axis is a date and there is room left for 6 days between plotted data. Try changing the x-axis format to type Text.

That worked! Man, thanks a lot for all your help. I'll let you know how the macros work out and I will probably have questions when I try to add the other column to it in order to have the macro auto format each data series that has 2 consecutive increases.