Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Bar Chart: Conditional formatting

1. I agreed to these rules
Join Date
8th December 2005
Posts
12

## Bar Chart: Conditional formatting

How does one apply conditional formats to the bars in a bar chart. I am measuring performances of various functions and have them recorded by percentage. The cells the percentages have conditional formats, ie., if performance drops below a certain percentage it turns yellow, then if it goes below a lesser percentage, it turns red. I cannot find anything on this anywhere yet I have seen it done before. Thanks and Merry Christmas.

Excel Video Tutorials / Excel Dashboards Reports

2. ## Re: Bar Chart: Conditional formatting

As Andy Pope has written elsewhere on the forum, conditional formatting on charts is very limited. My first thought to apprach your problem, is to change the appearance of the chart using vba. To record some code, use the macro recorder. You just record while you manually change the color of say a bar chart. Then go to the code in the modules in VBA. What you then have to do is, I'm just thinking loudly, substitute in the conditions you used in the conditional formatting, based on the cell values. That is, you use 1 or more ifs to assign for instance the color of a bar chart. The conditions are those you used in the conditional formatting as well. Merry Christmas to you too David. Let us know if you proceed.

Wigi
Last edited by Wigi; December 23rd, 2005 at 02:21.

3. I agreed to these rules
Join Date
8th December 2005
Posts
12

## Re: Bar Chart: Conditional formatting

Criteria:

<80% = Red
>=80% and <85% = Yellow
>=85% = Green

Excel Video Tutorials / Excel Dashboards Reports

4. ## Re: Bar Chart: Conditional formatting

As I said, go out experimenting with the macro recorder. Just select parts of the bar chart (those you want to change their color) and actually change it in a color like red. Then see how you should write that in VBA code. Then, change a few things to incorporate the conditional formatting. I suggest to play around with the macro recorder, and if you're stuck then, upload a small example file. It's difficult for us to see how your actual file looks like in detail. I cannot write in general the formulas you have to use.

Wigi
Last edited by Wigi; December 23rd, 2005 at 02:34. Reason: typo

5. ## Re: Bar Chart: Conditional formatting

Hi,

This is a non vba approach. You just need a series for each colour and a formula that only displays the value within the correct band.

6. I agreed to these rules
Join Date
29th November 2010
Posts
1

## Re: Bar Chart: Conditional formatting

Originally Posted by Andy Pope
Hi,

This is a non vba approach. You just need a series for each colour and a formula that only displays the value within the correct band.
Thanks Andy
U r a legend

Cheers

Excel Video Tutorials / Excel Dashboards Reports

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno