adjust color of bars in graph

Gespierde velopompe

New Member
Joined
May 20, 2010
Messages
45
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
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,002
Office Version
  1. 365
  2. 2016
Plot several series each of which relates to the cases in which you want to want the bars to change colour.
Excel Workbook
ABCDEF
1Raw Data4567
2Value16#N/A#N/A6#N/A
3Value25#N/A5#N/A#N/A
4Value36#N/A#N/A6#N/A
5Value47#N/A#N/A#N/A7
6Value544#N/A#N/A#N/A
7Value644#N/A#N/A#N/A
Sheet1
Excel 2007
Cell Formulas
RangeFormula
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

New Member
Joined
May 20, 2010
Messages
45
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

MrExcel MVP
Joined
May 28, 2002
Messages
11,028
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

Well-known Member
Joined
Jun 23, 2006
Messages
7,002
Office Version
  1. 365
  2. 2016

ADVERTISEMENT

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

Here's what I came up with
Excel Workbook
ABCDEFG
1ProductSalesTargetVariantBelowwithin 10%Above
2A7859292912-15%78592#N/A#N/A
3B1527138890-61%15271#N/A#N/A
4C62364586126%#N/A62364#N/A
5D895347153825%#N/A#N/A89534
6E6318622209185%#N/A#N/A63186
7F3242391672-65%32423#N/A#N/A
8G68739649606%#N/A68739#N/A
Sheet6
Excel 2007
Cell Formulas
RangeFormula
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

charts.png
 

Gespierde velopompe

New Member
Joined
May 20, 2010
Messages
45
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...

Code:
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

Well-known Member
Joined
Jun 23, 2006
Messages
7,002
Office Version
  1. 365
  2. 2016

ADVERTISEMENT

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

New Member
Joined
May 20, 2010
Messages
45
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

Well-known Member
Joined
Jun 23, 2006
Messages
7,002
Office Version
  1. 365
  2. 2016
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

New Member
Joined
May 20, 2010
Messages
45
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,961
Messages
5,621,847
Members
415,861
Latest member
Leetor72

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top