How To Modify This Formula To Automate Chart Bar Colours

Hi guys,

I have found this formula that automatically changes chart colours depending on the original data cell colour. However, I am facing a problem when the original data cell colour is set to "no fill" the chart bar still comes out as a solid opeque "white". In my intended use, it is blocking my underlying text as I have made the chart box "transparent", but this bar is appearing as a opeque white block.

Please refer to the attached, Tab Scratchpad first and then Dashboard as I wrote up a note to explain there. I'm doing up a simple dashboard and would like to have the chart colours automatically reflected to whatever editing in Tabs 2 & 3 (tabs Planned Project List and Planned Initiative List).

The idea is to have users editing only in tabs Planned Project List and Planned Initiative List and all the necessary changes will be automatically reflected on the Dashboard. The Scratchpad collects relevant data from tabs Planned Project Lists and Planned Initiative List to generate the Dashboard.

I have two issues:

1. Is there an automatic way to link the colour of a cell from tab Planned Project List and Planned Initiative to the Scratchpad?

2. The macro in I found (please see below) works well to automate the colour of the individual graph except that the 180 degree 'bar' would not turn to 'no fill'. Instead it comes up as a 'opaque white' which is blocking some details in the dashboard.

Please advise, thank you!

The macro I found:

Please Login or Register  to view this content.