Conditional Formatting - Detecting dates among other data + boldface

Hi all,

I am brand new to VBA programming and I'm not finding a solution to my problem using just formulas (although if there is a way to do this, I think it would be preferred).

I have a spreadsheet where I track appointments and I input the appointment title and date in BOLD once it is scheduled (e.g. "C1D22 6/20/17"). I use this to track data entry for a research study, so every patient has a series of appointments over time. These I add manually to my spreadsheet horizontally. Each patient is listed vertically in column G.

What I want my spreadsheet to do is this:

1. Detect when a cell to the right of G# meets these two conditions: is BOLDFACE *and* contains a date that is in the past.
2. Any date in bold AND 1-8 days old, I want to turn ORANGE
3. Any date in bold AND 9+ days old, I want to turn RED
4. If I turn the cell any other color manually (like green or yellow) or if I remove the boldface, I want this to override the above rules.
5. Finally, I want the cell in column G that contains patient initials to turn the corresponding color if ANY cells to the right contain the following (in this order):

- IF any are RED, make initials RED
- If no red but ORANGE, make initials ORANGE
- IF no orange or red, but YELLOW (manually colored yellow), make initials Yellow.
- IF no color or if only GREEN, make initials GREEN.

Thank you for any help!

ExcelQuestion.JPG

Have you tried doing this with Conditional Formatting?

And perhaps post a sample to start with?