Conditional format - three criteria

[tesa72]

Hello! I'm having a problem trying to add conditional format to a project schedule I'm working on. I'm trying to have two things happen.

1. format with diagonal lines based on date falling within a certain range. What I have set up for that works: =AND($E8>=H$5,$D8<I$5)
2. I'm trying to have the same cells formal with a solid color when the task has been completed but can't get that to override the previous format when applicable. I've tried =AND($E8>=H$5,$D8<I$5,$C$8=100)

I've tried re-ordering the rules but it's not working. Any help would be greatly appreciated!!

 

[DRSteele]

Welcome to the forum.

I guess what you need to do is put your second rule at the top of the list and then select Stop If True.

 

[tesa72]

Welcome to the forum.

I guess what you need to do is put your second rule at the top of the list and then select Stop If True.

Thank you for the suggestion! I've tried that but it's not working. I'm a bit baffled.

 

[DRSteele]

Please post the sample data so we can conduct experiments. Use XL2BB - Excel Range to BBCode

 

[tesa72]

Please post the sample data so we can conduct experiments. Use XL2BB - Excel Range to BBCode

I'm so sorry but I can't figure out how. I downloaded the software and was able to save it to the clipboard but can't figure out how to post it.

 

[DRSteele]

Simply paste it into the edit box here. It will appear as code; you can click on Preview at the top right of the edit box and then Post Reply.

 

[tesa72]

Ignite Class Schedule.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
8
9		Consumer Direct 1	6%	7/12/21	12/24/21	1/0/00	7/30/21
10		Week 1: SAFE Prep	100%	7/12/21	7/16/21	7/12/21	7/16/21
11		Week 2: SAFE Prep	0%	7/19/21	7/23/21	7/19/21	7/23/21
12		Week 3: SAFE Prep/Exam	0%	7/26/21	7/30/21	7/26/21	7/30/21
13		Week 4: State Licensing/5 Star	0%	8/2/21	8/6/21
14		Week 5: Sales and Systems 101	0%	8/9/21	8/13/21
15		Week 6: Sales and Systems 102	0%	8/16/21	8/20/21
16		Week 7: Sales and Systems 103	0%	8/23/21	8/27/21
17		Week 8: Go Live / On-demand help	0%	8/30/21	9/3/21
18		Week 9: Live: Incubation / Daily Training (30 mins)	0%	9/6/21	9/10/21
19		Week 10: Live: Incubation / Daily Training (30 mins)	0%	9/13/21	9/17/21
20		Week 11: Live: Incubation / Daily Training (30 mins)	0%	9/20/21	9/24/21
21		Week 12: Live: Incubation / Daily Training (30 mins)	0%	9/27/21	10/1/21	1/0/00
22		Week 13: Live / Training (30 mins, 2 x week)	0%	10/4/21	10/8/21
23		Week 14: Live / Training (30 mins, 2 x week)	0%	10/11/21	10/15/21
24		Week 15: Live / Training (30 mins, 2 x week)	0%	10/18/21	10/22/21
25		Week 16: Live / Training (30 mins, 2 x week)	0%	10/25/21	10/29/21
26		Week 17-24: Live / Training (30 mins,1 x week)	0%	11/1/21	12/24/21
Class Schedule - Detail View

Cell Formulas
Range	Formula
C9	C9	=AVERAGE(C10:C26)
D9,F9	D9	=MIN(D10:D26)
G9,E9	G9	=MAX(G10:G26)
E10:E25	E10	=D10+4
D11:D26	D11	=E10+3
F21	F21	=F22
E26	E26	=D26+53

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
L10	Expression	=COUNTIF(Holiday!$C$3:$C$12,$D$10:$E$188)>1	text	NO
C8:C190	Other Type	DataBar		NO
H8:CB190	Expression	=AND($E8>=H$5,$D8<I$5)	text	NO
H8:CB190	Expression	=AND($E8>=H$5,$D8<I$5,$C$8:$C$188=100)	text	NO
H8:CB190	Expression	=NOT(AND(MAX($G8,$E8)>=H$5,MIN($F8,$D8)<I$5))	text	NO

 

[DRSteele]

Move =AND($E8>=H$5,$D8<I$5,$C$8:$C$188=100) to the top of the list (at least above the yellow one) in the CondFrmt dialog box and select the Stop If True checkbox next to it.

 

[DRSteele]

Oopth. I didn't mean the yellow one. I meant the one with =AND($E8>=H$5,$D8<I$5)