adjust color of bars in graph

[Gespierde velopompe]

Good evening all,

Will try my luck again on the forum.

I'm working with some graphs and on a weekly or monthly bases the color of the bars needs to be changed according to the result of that certain week/month.
As this is a lot of work I'd like to write a macro for it. If I click on the button, the macro should work as follows:

1. Check the value of that certain week/month and compare it with the target that should have been reached in that week/month.
ex. result week 1 = 6
target week 1 = 7

2. Once the comparison has been made, it should adopt the color of that the bar based on the result of the comparison.
ex. the target of week 1 wasn't reached as the target was 7. So in this case the bar should be colored red.

I need some help from you guys on this one.

Thanks in advance

 

[dave3009]

Plot several series each of which relates to the cases in which you want to want the bars to change colour.

Excel Workbook
	A	B	C	D	E	F
1		Raw Data	4	5	6	7
2	Value1	6	#N/A	#N/A	6	#N/A
3	Value2	5	#N/A	5	#N/A	#N/A
4	Value3	6	#N/A	#N/A	6	#N/A
5	Value4	7	#N/A	#N/A	#N/A	7
6	Value5	4	4	#N/A	#N/A	#N/A
7	Value6	4	4	#N/A	#N/A	#N/A
Sheet1

Excel 2007

Cell Formulas
Range	Formula
C2	=IF($B2<>C$1, NA(), C$1)

In this case you'd plot miss out plotting the Raw Data column, change each bar colour to suit

HTH


Dave

 

[Gespierde velopompe]

Hi Dave,

That won't do it for me. the way I'd like to do it (if possible) is as follows:

1) selecting a certain bar
2) checking what the value of that bar is
3) compare that value to the target
4) decide which color the bar should have.

For now I can only do step 3 myself. For the other step I need a push in de right direction.

 

[tusharm]

Dave's solution will work for you. Just adapt it to have only 1 threshold and to test for < or >= rather than equality.

Hi Dave,

That won't do it for me. the way I'd like to do it (if possible) is as follows:

1) selecting a certain bar
2) checking what the value of that bar is
3) compare that value to the target
4) decide which color the bar should have.

For now I can only do step 3 myself. For the other step I need a push in de right direction.

 

[dave3009]

Hmm, doing it on a click basis might prove hard work.

Here's what I came up with

Excel Workbook
	A	B	C	D	E	F	G
1	Product	Sales	Target	Variant	Below	within 10%	Above
2	A	78592	92912	-15%	78592	#N/A	#N/A
3	B	15271	38890	-61%	15271	#N/A	#N/A
4	C	62364	58612	6%	#N/A	62364	#N/A
5	D	89534	71538	25%	#N/A	#N/A	89534
6	E	63186	22209	185%	#N/A	#N/A	63186
7	F	32423	91672	-65%	32423	#N/A	#N/A
8	G	68739	64960	6%	#N/A	68739	#N/A
Sheet6

Excel 2007

Cell Formulas
Range	Formula
D2	=(B2-C2)/C2
E2	=IF(D2<-10%, B2, NA())
F2	=IF(AND(D2>=-10%, D2<=10%), B2, NA())
G2	=IF(D2>10%, B2, NA())

And the resulting chart

 

[Gespierde velopompe]

Dave,

This can do it indeed.

I just need help with one other thing and that is with my step 2:

2) checking what the value of that bar is

I tried several codes (like below), but didn't seem to work...

ActiveChart.SeriesCollection(3).Points(7).value
ActiveChart.SeriesCollection(3).Points(7).values
ActiveChart.SeriesCollection(3).Points(7).Xvalues

it's possible to add certain value to a bar, so it should be possible to do the opposite or I'm wrong?

Thanks

 

[dave3009]

Do you mean you want to add data labels to the bars? This is an option in the properties of the data series of the chart

 

[Gespierde velopompe]

Hi Dave;

Sorry for the late reply. I don't want to add data labels, in fact I'd like to know if it would be possible to retrieve the value of a certain datalabel using VBA?

thnx.

 

[dave3009]

Would the data label not reside on the sheet somewhere in which case you could look it up then ask VBA to return it for you in what ever context you need VBA to return it.

 

[Gespierde velopompe]

Hi Dave,

That is the problem I have and that is also the degree of difficulty. There are multiple graphs and the data come from several sheets, so there is no way I can trace those figures or I should be making a seperate code for maybe 50 graphs.

Thanks.