VBA Conditional Formatting - Change Font Color across row per 1st column (6 colors)

I need to find VBA code for:
-- Changing the font color of part of a row (within a named range), based on the value of the cell in the first column of the row (within the range)
-- I need at least 6 colors, each with a different criterion, so regular conditional formatting won’t work (I have Excel 2003).

The file is a calendar for managing the schedules of employees of a small department, formatted as the attached fragment of the whole. The first column within each block of cells (block = a single date on the calendar) is a pull-down list of the initials of the employees. As I select a different employee or change that first cell, I want the font color to change for that cell and the cells to the right within that date block.

I think I’ll need a different section of code for each day-of-the-week range, which I’ve named “Sunday” “Monday”, “Tuesday”, etc.

I’d prefer to reference the employees by the named cell which contains their initials, i.e. “Name1” “Name3” “Name3”, etc, rather than the actual initials (“KG” “SB” etc), since employees may change.

I know the color numbers for what I want:
Name1 – 1
Name2 – 3
Name3 – 4
Name4 – 7
Name5 – 5
Name6 – 46

I’ve searched and tried various solutions from the web, but none seem to work quite right. The ones I’ve tried result in error messages, or the color for a particular cell gets set and then doesn’t change, even if I change the value.

Thanks!

John