Excel - Conditional formatting

You realize, I trust, that you're asking us to imagine how your workbook looks and how it functions, and then (based on whatever we've concocted in our imaginations) come up with specific ways to accomplish what are in fact some pretty sophisticated "tricks."

You'd make it a lot easier to for us to give you the requested assistance if you posted either a copy of your actual workbook or (if you need to protect proprietary or private information) a representative mockup of the actual. If you can, attach it to your next reply here in this forum. If you're not able to do that, post a copy in OneDrive or GoogleDrive and paste a link here that grants access.

All of that having been said, I would encourage you to make use of some on-line resources and give yourself permission "to play" with the methods of conditional formatting and data validation. Work with a copy of the actual workbook, but give yourself permission to engage in trial and error--it's the best way to learn. Kidd_Ip​ has already given you some official Microsoft links. I personally find even more helpful the site ExcelJet.Net, where his examples help flesh things out.