Conditional Formatting by formula - need assistance

Hello Pros,

I have over the years I've learned much from this site, only this time I think I actually have to post a question as I am in need for a specific answer.

I have made an employee schedule, I have all the coding down to do exactly what I need it for. Upon its implementation my team did not like that I got rid of the color fills for each cell that were associated with their shifts. I didn't want to make a separate sheet and list all the possible combinations of shifts to determine which color to code the cell then refer back to that sheet as I had done in the past. That process was searching for a specific shift and would color it. But if i made an odd shift such as 8a -9a it would not know what to do as it was not a defined shift.

On my new schedule I wanted to use conditional Formatting to search the cell for the first 3 characters/number and determine it as a start time for a shift. Using an defined table that I called "ShiftData" the formula was to search the cell and find the start time and search the table "ShiftData" for a shift code. From that shift code it would know to color all cells that color. I don't know if this is the easiest way or not, I was recycling code i used on my old schedule to make it work, only conditional formatting will not do what i want no matter how i code it. This is where i need help.

This is the code that has been giving me the best results only its coloring everything else but what should be that color:
=IF(B14="OFF","0",IF(B14="R/OFF","0",IFERROR(VLOOKUP(SUBSTITUTE(SUBSTITUTE(LEFT(B14,3)," ","",1),"-",""),ShiftData,2,FALSE),0)))

If I had this in a cell it would work and give me the 1A result, so i know the formula works, i just don't know how to get it to apply to conditional formatting so that all 1A shifts are colored yellow.
Any assistance would be greatly appreciated.

Where in your attachment does it show what you want the sheet to look like? Is there a key somewhere?

=IF(B14="OFF","0",IF(B14="R/OFF","0",IFERROR(VLOOKUP(SUBSTITUTE(SUBSTITUTE(LEFT(B14,3)," ","",1),"-",""),ShiftData,2,FALSE),0)))

When creating conditional formatting rules, you need rules that give a TRUE or FALSE result. What, in WORDS, are you expecting this formula to do - step-by-step, please.

Thanks! That gets us halfway there. I found this:

Excel 2016 (Windows) 32 bit

	C	D	E	F	G	H
67	Shift Data
68	1A	5
69	2A	5			Morning	1A
70	3A	5			Mid-Day	2A
71	4A	5			Evening	3A
72	5A	1A			Pre-Audit	4A
73	6A	1A			Audit	5A
74	7A	1A
75	8A	1A
76	9A	2
77	10A	2
78	11A	2
79	12P	2
80	1P	3
81	2P	3
82	3P	3
83	4P	3
84	5P	4
85	6P	4
86	7P	4
87	8P	4
88	9P	4
89	10P	5
90	11P	5
91	12A	5

Sheet: Expected Look

Could you please explain it and how I apply it to the actual data to decide on the colour? I need to understand the logic before I can create a rule.

Right - OK, I think I understand. Give me a few minutes to work on this.

OK, so you've changed things a bit since I've been working on this.

Yellow is:

=OR(LEFT(B11,2)="5A",LEFT(B11,2)="6A",LEFT(B11,2)="7A",LEFT(B11,2)="8A")

Blue:

=OR(LEFT(B11,2)="9A",LEFT(B11,3)="10A",LEFT(B11,3)="11A",LEFT(B11,3)="12P")

Green:

=OR(LEFT(B11,2)="1P",LEFT(B11,2)="2P",LEFT(B11,2)="3P",LEFT(B11,2)="4P")


Dark blue:

=OR(LEFT(B11,2)="5P",LEFT(B11,2)="6P",LEFT(B11,2)="7P",LEFT(B11,2)="8P")

Mauve:

=OR(LEFT(B11,3)="12A",LEFT(B11,2)="1A",LEFT(B11,2)="2A",LEFT(B11,2)="3A",LEFT(B11,2)="4A")


All need applying to the range =$B$11:$H$40

Yes. It works here. Did you apply it to the range =$B$11:$H$40?

Please attach a new version of the file with your attempt - I can't troubleshoot screenshots!

OK - lots of things wrong. Edit it again and make sure that all that is in the yellow rule is this (other rules listed above):

=OR(LEFT(B11,2)="5A",LEFT(B11,2)="6A",LEFT(B11,2)="7A",LEFT(B11,2)="8A")

At the moment you have two = and too many " - it must be EXACTLY as the above - copy and paste it in.

You're welcome!

Glad we got there in the end ...