Shade single bar in a bar chart...

Posted on 2014-04-21
Medium Priority
Last Modified: 2014-04-22
The attached image shows a combination of line and bar chart. The named-range called CurrMthCol may contain numbers from 1 to 24 (a two year period).

In the attached image, because it contains Agust 2013 (or 8), its matching bar is shaded (filled) red.

For your consideration, Agust 2014 will be 12 + 8 or 20.

Question: Using VBA or whatever possible, how can I shade the bar matching the value in CurrMthCol?

Thank you.
Question by:Mike Eghtebas
LVL 50

Accepted Solution

Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 2000 total points
ID: 40013906

the description of the data and layout is a bit unclear. Maybe you can post a sample file instead of  a picture.

What you want to achieve can be done without VBA, just using data layout. In the attached file you can see a stacked column chart from two series. The chart data is in two helper columns. A formula determines if the white bar has a value greater than 0 or the red bar has a value greater than 0.  You will need to figure out the logic for your situation, but we can help with that, of course.

Whenever the number in cell G2 changes, the red bar in the chart will shift its position.

Let me know if this helps or if you need more assistance.

cheers, teylyn
LVL 34

Author Closing Comment

by:Mike Eghtebas
ID: 40015298
Thank you.

Featured Post

Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

622 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question