Conditional Formatting a Graph

[markkara]

Hi Guys


I want to conditional format a bar graph based on the results within B3-B14. Ive applied a conditional format to B3-B14, in which anything equal or greater than 80 is green and anything less than 80 as red.


Essentially i want the graph results for that given month to replicate the color of the cell, but not sure how this can be done. Im assuming via a macro, however my VBA/recording skills are limited at this point (still playing around with it).


Can someone explain how i can run a macro automatically change the call of the bar as per the respective cell?


I mean, i could change the bar color via a formatting tool, but i want this to be as quick and proffesional as possible.


Here is the link to my dummy file:


http://www.sendspace.com/file/nuia2e


Help is appreciated.

 

[Hui]

Markkara


Firstly, Welcome to the Chandoo.org Forums


Have a read of Narayan's answer to this post:

It sounds like it should help you

http://chandoo.org/forums/topic/set-chart-series-colors-to-match-source-cell-colors-based-on-conditional-format

 

[dan_l]

Bah.


http://dl.dropbox.com/u/1275899/Book1.xlsm


Here's what I did:


1. I created 2 new columns. One is for values over target, the other is for values below your target.

2. Using a couple of if/then's, I added formulas to go all the way down to December. If the value isn't populated yet, it just inserts "" or nothing.

3. This is just some polish, but for something you'll be updating, it's kind of nice: I added 3 dynamic ranges to the chart. If you click on your name manager, you'll see them.

4. I plugged the 3 dynamic ranges into a chart.


Now, if you add data in to your original column, you'll see the chart automagically puts the value in the right bucket and the chart updates accordingly.

 

[markkara]

Thanks for your reply Dan, this is exactly what i was after.


Is it possible for you to explain in greater depth for my knowlegde the use of the if/then and the dynamic ranges?


I want to use this in future and apply it to various sheets, so id like to brush up on the theory behind the actual practise and output.


Thanks!

 

[dan_l]

Sure.


The DNR's are based on the offset formula. You can read the official chandoo explanation for them here:


http://chandoo.org/wp/2009/10/15/dynamic-chart-data-series/


But more or less, you have 3 of them in this workbook. If you peek at your name manager you'll see:

=OFFSET(Sheet1!$D$3,0,0,COUNT(Sheet1!$C$3:$C$14),1)

=OFFSET(Sheet1!$A$3,0,0,COUNT(Sheet1!$C$3:$C$14),1)

=OFFSET(Sheet1!$C$3,0,0,COUNT(Sheet1!$C$3:$C$14),1)


I always have trouble remembering the syntax for offset, so i always remember it as:

=offset(starts where,rows over, columns over, height, width)


So in this case you're starting at the top sells of our key ranges. We're not modifying the starting position. We could. But we're not, so rows over and columns over are 0. The moving part is the count function. You'll notice that I don't change it. That's because I'm lazy. That and since you already have all of the months listed, a more accurate reading on how many labels you need would be taken from one of the data columns. Width would always be 1. I chronically put it in as 0 by accident.


On to the if's, they're pretty self explanatory:


=IF(B3<>"",IF(B3<80,B3,0),"")


If you're new, you might be just thrown off by the fact that there's 2 if's in there:


All it's saying is: If B3 is not blank, check to see if the contents are less than 80, otherwise return a blank.


As long as it's returning "" or blank, it won't be picked up by the count function in the offset formula.

 

[markkara]

Thanks Dan, appreciate the extended answer.


One final thing. Ive recreated the book using your steps and ive gotten this to work, however the color coding is the typical red and blue that excel defaults on. For above 80 im getting the red, below 80 im receiving the blue.


In your examples, it seems to have already been programed as green and red, what step is the color process mapped?


Thanks

 

[dan_l]

Just use the formatting options. You'll only have to do it once.