Hi Bloodywog,
I like the look and feel of your spreadsheet. Please see the code that follows and the attached file (in Excel 2003 format). You should be able to use the macro in your spreadsheet, after you remove conditional formatting. Please note that CONDITIONAL FORMATTING takes precedence over other colors.
I had problems with employee substitution, until I made a small change to your symbols. If someone is substituting for Employee 5 on the night shift, I changed the designation from 'N' to 'N5'. After I did that, it was relatively easy using VBA to color your roster.
Here is how I approached your problem:
a. All conditional formatting was removed.
b. There are colors in Column 'A' for each employee (cells A3 thru A10 are the MASTER COLORS).
c. There are colors for SL, RL, and Error in cells A53 thru A55.
Lewis
Code follows:
Bookmarks