Excel 2010 Legend key color based on text

Question

Background

I've an excel template. This template I populate with data by running a program I've written. The excel template has a pivot table on a sheet that get's data from another sheet that I've updated. The pivot table has two columns and a connected chart.

The first column one is "Status" and the other is "Count of x". The first column contains a word like Implemented, Rejected, Issued, Accepted etc. All these words are known. The second column contains a number.

The rows in the pivot table varies. Sometimes there are only Implemented and rejected and sometimes more rows with values.

What I want

I want to be able to specify the legend key and connected pie chart color based on column one's text. So that Implemented is always green, rejected is always red etc.

If it's possible to write code that checks if the value is equal to one of the words and set the color based on those, that would be interesting.

Thanks

There is a bug. If the excel get's macro enabled when the chart page is active excel remembers the color formatting. If another page is active when the user enables macro then the color formatting is forgotten by excel.

Answer 1

First, you need to create the chart, and whenever you're done. You only need to select the Status column and go to Home tab, and use Conditional Formatting to make the proper fill and font colors on cells based on a specific text.

To do that, you will need to go to the Conditional Formatting and then choose Manage Rules.

From the Manage Rules dialog, add a New Rule.

From the New Formatting Rule dialog, Do the following:

• Select a Rule Type : Format only cells that contain
• Edit the Rule Description: Specific Text | containing | "Your Status"(put your status between quotes)
• Then, click Format button and choose the proper colors you need, then click OK.
• Now, click OK.

Repeat the steps until you cover all status. Then, click OK and you're done.

Here is an example of what it will looks like :

As you can see, I created the chart first, then I added 5 rules on the column A:A for each word same steps that I explained earlier, and then match the cells colors with the chart colors. The total table is just to get the total number of each status, which I used to create the chart. (I used SUMIF to sum all numbers with matched status)

Answer 2

Not tested (I don't have Excel 2010 and charting there is not the same as Excel 2007) but I think the following worth trying, unless you get a better offer.

1. Ensure your source data contains each of the Status options. (To force at least a single instance of each into the pivot table driving the pivot chart. Don't hide blanks.)
2. Conditionally format the pivot table to suit chosen colour scheme (so it matches with pie).
3. Create your pie chart with colouration to suit and save as template.

Pitfall could be having to reimpose the template format each change, but that's little more than a pivot table refresh.