Conditional Formatting of Pie Chart

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).