# adjust color of bars in graph

#### Gespierde velopompe

##### New Member
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.

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

#### dave3009

##### Well-known Member
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
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
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

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

#### Gespierde velopompe

##### New Member
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

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
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
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
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.

Replies
5
Views
100
Replies
2
Views
117
Replies
3
Views
249
Replies
2
Views
63
Replies
0
Views
39

1,127,199
Messages
5,623,323
Members
415,966
Latest member
ctorohuamanchumo

### 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.

### Which adblocker are you using?

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

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