Having a cell flag red (or something) when a name is listed as off.

[szm187]

So, I have a portion of the spreadsheet pulled out that can represent what I want on a basic level.

Basically, I have two columns that people's names will be put into on the left hand side.

On the right, I have a list of times people are in.

I'm going to ask two levels of questions, one I think is pretty easy, one which might be a lot more intense.

So Level 1 Question:
I would like it so that anytime a person's name is listed as "OFF" in column G that the cell they are entered in turns red or does something to signify that an off scheduled person is in the schedule. So, in the attachment, if Carl or Greg was entered in cells B2-B12 or C2-C12, that cell would turn red (or something equally significant if there's a simpler way)

Level 2 Question (And I don't know if there's an easy answer to this)
I would like it so that anytime a person's name is listed in a cell that they are not scheduled for, that the cell would turn red. Very important note, the times in column G will change often, so the question is not for just this one instance, but for any combination I would enter.

Like I said, Level 1, I'm sure there's a fairly simple answer. Level 2, not so much. If you can answer either or, your help would be very appreciated. Thanks!

[FDibbins]

These can both be done with Conditional Formatting.

If you are familiar with CF, ignore the 1st 2 points (B2:C12 in your sample)
1. highlight the range you want to apply the conditional formatting to
2. on the home tab, styles, select CF
3. select new rule, select use formula

for Q1...
4. enter =VLOOKUP(B2,$F$2:$G$12,2,0)="OFF"

For Q2...
You will (I think) need to break those "times" - they are not really times/values, they are text - onto their 2 component parts

[FDibbins]

This is what I have so far, it does not work (yet) but I am out of time right now, will take another look in about an hour...
=AND(VLOOKUP(B17,$F$17:$I$27,3,0)<=$A17,VLOOKUP(B17,$F$17:$I$27,4,0)>=$D17)

	A	B	C	D	E	F	G	H	I
16
17	8:00	Sheri		9:00	TRUE	Sheri	08:00 - 16:30	8:00:00 AM	4:30:00 PM
18	9:00	Sheri		10:00	TRUE	Melina	09:00 - 17:30	9:00:00 AM	5:30:00 PM
19	10:00	Sheri		11:00	TRUE	Lora	10:00 - 18:30	10:00:00 AM	6:30:00 PM
20	11:00	Sheri		12:00	TRUE	Carl	OFF	OFF	OFF
21	12:00	Sheri		13:00	TRUE	Bob	11:30 - 20:00	11:30:00 AM	8:00:00 PM
22	13:00	Sheri		14:00	TRUE	Mandy	12:30 - 21:00	12:30:00 PM	9:00:00 PM
23	14:00	Sheri		15:00	TRUE	Marcus	PTO	PTO	PTO
24	15:00	Sheri		16:00	TRUE	Jack	TRAINING	TRAINING	TRAINING
25	16:00	Sheri		17:00	FALSE	Mary	08:00 - 16:30	8:00:00 AM	4:30:00 PM
26	17:00	Sheri		18:00	FALSE	Greg	09:00 - 17:30	9:00:00 AM	5:30:00 PM
27	16:30	Sheri		17:30	FALSE	Eric	10:00 - 18:30	10:00:00 AM	6:30:00 PM

[FDibbins]

OK, back.

In the table below, I broke out your "times" into real times into columns A, D, G and H...

	A	B	C	D	E	F	G	H
17	8:00	Sheri		9:00		Sheri	8:00:00 AM	4:30:00 PM
18	9:00	Sheri		10:00		Melina	9:00:00 AM	5:30:00 PM
19	10:00	Sheri		11:00		Lora	10:00:00 AM	6:30:00 PM
20	11:00	Sheri		12:00		Carl	OFF	OFF
21	12:00	Sheri		13:00		Bob	11:30:00 AM	8:00:00 PM
22	13:00	Sheri		14:00		Mandy	12:30:00 PM	9:00:00 PM
23	14:00	Sheri		15:00		Marcus	PTO	PTO
24	15:00	Sheri		16:00		Jack	TRAINING	TRAINING
25	16:00	Sheri		17:00		Mary	8:00:00 AM	4:30:00 PM
26	17:00	Sheri		18:00		Greg	9:00:00 AM	5:30:00 PM
27	16:30	Sheri		17:30		Eric	10:00:00 AM	6:30:00 PM

Then the use this as a new rule - use Formula...
=OR(VLOOKUP(B17,$F$17:$H$27,2,0)>$A17,VLOOKUP(B17,$F$17:$H$27,3,0)<=$D17)

[szm187]

These can both be done with Conditional Formatting.

If you are familiar with CF, ignore the 1st 2 points (B2:C12 in your sample)
1. highlight the range you want to apply the conditional formatting to
2. on the home tab, styles, select CF
3. select new rule, select use formula

for Q1...
4. enter =VLOOKUP(B2,$F$2:$G$12,2,0)="OFF"

For Q2...
You will (I think) need to break those "times" - they are not really times/values, they are text - onto their 2 component parts

So, looking at the answer for Question 1,

It's not working when I enter that formula, and I'm trying to deduce why.

B2 references what we're using as the comparison. I get that.
$F$2:$G:12 is the range of where we're getting the comparison
,2 is the 2nd of the columns I believe, referring to whether or not time or Off or whatever is there
,0 I have no idea why that's there
=OFF is obviously what we're looking for.

The thing is, on my full spreadsheet, the times are a bit more locked in to other places, so it's good that I'll know how to break that down if need be, but I'm hoping this simpler method will make it acceptable . If you could help me translate where I'm missing something, that would be great!

[szm187]

Haha, answered my own question.

I wasn't putting the quotes around Off.

Okay, I'll leave that there because reasons. Thank you so much for your help!