Conditional Formatting of Pie Chart

[leviathanjc]

Hello everyone,

My excel skills have been progressing at a rapid rate, but unfortunately I am still quite clueless when it comes to VBA and macros.

I want my pie chart which has dynamically changing number of wedges to also reflect the conditional formatting colouring used in the data table.

The data in column 'E' can either be Caution, On Watch, On Track, or N/A. If it is N/A, then i do not want it to show in the pie chart.

In my attached example, we have 4 wedges, but this is subject to change based on the outputs in Column E.

The wedges to be displayed will change from situation to situation.

I would like the employment income wedge to be orange, investment income to be red etc etc so that the user is able to simply look at the pie chart and immediately you are able to see the relevant areas to your situation (based on whether there's a rating in column E or if it's N/A), and how you are doing in each relevant wedge.

I have watched youtube videos and searched on google, where I have found a VBA code which I have tried to implement.

The issue is, once I click the button to run the macro, my entire chart goes white/blank.

Anyone able to shed some light on this? (PS: My original spreadsheet has 3 similar pie charts for different categories in the one sheet if that changes things).

[dflak]

This is an adaptation of similar code I wrote for someone else. I made a couple of changes to the basic design.

I converted the basic data in columns C:E to an Excel Table because the existing code I have looks at a table.

I changed the equation in the value column to: =IF(E47="N/A",NA(),1) - this assigns a real #N/A. Real N/As don't plot on charts.

I added data validation for the Status. You can now select the values using a drop-down list.

I have an on change event on the sheet that runs the program when you change any of the statuses.

[leviathanjc]

Hi dflak, i've attached another dummy version of the spreadsheet as per our discussions.

Thank you again!

[dflak]

I think this is it, but there are a couple of discrepancies.

The cashflow chart does not seem to be linked to the data in Columns C:E. Instead it seems to be linked to data in columns U:Y, so it doesn't work.

For the other charts: when N/A is selected, the wedge turns white instead of disappearing. I think this might have to do with the fact that the range is in the cloud and linked to something not on the sheet. This might go away if you read the dropbox settings onto the sheet and rebuild the pie chart using sheet data instead of linking the pie chart directly to dropbox. I do not know for sure.

The charts are updated by firing a change event with code on the Output sheet. When you change a value in Column E, it updates the appropriate chart based on the row of data selected.

There is a map sheet. You can ignore it or delete it. I decided to handle what to update in the change event.

[amir.abozead]

Thank you for your efforts & reply