• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Creating a Dashboard Conditional Formatting

scollops

New Member
Hi All,

I am just starting out creating a dashboard - using shapes that I want to change color based on current maturity values and GAP. I have raw data that has current, target and Gap numbers. I really appreciate the help if anyone can assist, but if VBA code help me understand what it is doing so as I add more to the dashboard I should be able to do without (hopefully ) too many hiccups.

I tried looking at a few on here - but cant quite get my head round the VBA code - maybe I will with my own data ?

Very much appreciate the assistance. Find attached example.
 

Attachments

  • TrainingExcel.xlsx
    15.7 KB · Views: 7
Here's a starter for you.
When you change the value (either 0, 1, 2 or 3) of the GAP for the Backup Maturity on the RawData sheet, the Data Backup shape colour on the Dashboard sheet changes.
It is very simple code with no error checking!
Click the 'RawData' tab and View Code, you'll see the code that detects the change of the cell value and, if the relevant cell has changed, calls a subroutine saved in the code Module1.
 

Attachments

  • TrainingExcel.xlsm
    24.1 KB · Views: 9
Here's a starter for you.
When you change the value (either 0, 1, 2 or 3) of the GAP for the Backup Maturity on the RawData sheet, the Data Backup shape colour on the Dashboard sheet changes.
It is very simple code with no error checking!
Click the 'RawData' tab and View Code, you'll see the code that detects the change of the cell value and, if the relevant cell has changed, calls a subroutine saved in the code Module1.
Paul - much appreciated, struggled with this for ages. Can I ask, do I need to recreate the entire code for each Box on the dashboard or can you alter the existing ? Sorry just thinking the dashboard will have some 15-20 boxes with more rawdata long term ?
 
The code to change colours can be altered to suit different shapes, see example attached for the first two shapes on your dash.
 

Attachments

  • TrainingExcel.xlsm
    24.6 KB · Views: 8
The code to change colours can be altered to suit different shapes, see example attached for the first two shapes on your dash.
Thanks Paul - I noticed the conditional formatting was removed on the Gaps......can I not calculate the difference automatically ? Seems this dosent work but fine if you input a figure ? (took me a while to figure out why colors where different). Paul I assume I click on the Rawdata tab for the code ? Which Code do I amend for adding more few boxes popped up - can I clarify this key one please. Thank you again.
 
I've put back your formulas and altered the code so that if either of the cells C5 or D5 change, then the Data Backup box colour is changed. Likewise, if either C6 or D6 is changed then the Data Storage box colour is changed. See the Sheet2(RawData) module for those and add what you need for the others.

The main routine is in Module1, change those values to suit your needs.
 

Attachments

  • TrainingExcel.xlsm
    25.2 KB · Views: 7
I've put back your formulas and altered the code so that if either of the cells C5 or D5 change, then the Data Backup box colour is changed. Likewise, if either C6 or D6 is changed then the Data Storage box colour is changed. See the Sheet2(RawData) module for those and add what you need for the others.

The main routine is in Module1, change those values to suit your needs.
Bingo !!! Thank you
 
And added the other two boxes to the code so you can clearly see the pattern.
 

Attachments

  • TrainingExcel.xlsm
    25.4 KB · Views: 18
Back
Top