Hi !
I would like to help you on your project. I have done a number of work on spreadsheet involving conditional formatting through my years (since university years) of using MS Excel, up until now in my main work as Technical Manager for a construction company in the Philippines.
Your first one seems relatively simple. It would involve setting up the conditional formatting parameters on MS Excel.
The next one, I would suggest, instead of summing up based on cell colours (as this would required Macros development), to sum up based on the values in each cell using the SUMIF or SUMIFS formula.
Also it might also be faster if you could share a sample of your working file for me to work on, and I could write up a documentation detailing the steps that I did in achieving your requirements.
Please drop me a line to discuss further. Thank you in advance !
Best regards,
Tristan