Conditional formatting with multiple criteria

[ladygermain]

Hi there,

First time poster here, any help someone can offer with my conditional formatting problem will go a long way!

I have a worksheet with worksites listed in the Column A and the days in a year listed across Row 1.

In a second worksheet I have the following data:
Column A: Worksite
Column B: Date site inspection began
Column C: Date site inspection ended

How can I construct a conditional formatting formula that will highlight cells in worksheet1 to show the days on which a worksite was being inspected based on the data in worksheet2?


Thanks in advance!

 

[ravi2628]

Hi

please try this.
Sheet1:

Book1
	A	B	C	D
22	Calender Date		Please Select the Site	W3
23	30-Sep-21
24	01-Oct-21
25	02-Oct-21
26	03-Oct-21
27	04-Oct-21
28	05-Oct-21
29	06-Oct-21
30	07-Oct-21
31	08-Oct-21
32	09-Oct-21
33	10-Oct-21
34	11-Oct-21
35	12-Oct-21
36	13-Oct-21
37	14-Oct-21
38	15-Oct-21
39	16-Oct-21
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A23:A39	Cell Value	between VLOOKUP($D$22,Sheet2!$K$1:$M$4,2,0) and VLOOKUP($D$22,Sheet2!$K$1:$M$4,3,0)	text	NO

Cells with Data Validation
Cell	Allow	Criteria
D22	List	=Sheet2!$K$2:$K$4

Sheet2:

Book1
	K	L	M
1	Worksite	Date site inspection Began	Date site inspection ended
2	W1	01-Oct-21	02-Oct-21
3	W2	04-Oct-21	07-Oct-21
4	W3	10-Oct-21	11-Oct-21
Sheet2

 

[ravi2628]

Hi May be this will be very use full and easy to get.

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Work Site	Start Date	End Date	30-Sep-21	01-Oct-21	02-Oct-21	03-Oct-21	04-Oct-21	05-Oct-21	06-Oct-21	07-Oct-21	08-Oct-21	09-Oct-21	10-Oct-21	11-Oct-21	12-Oct-21	13-Oct-21	14-Oct-21	15-Oct-21
2	W1	02-Oct-21	03-Oct-21
3	W2	04-Oct-21	06-Oct-21
4	W3	12-Oct-21	13-Oct-21
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D5:I10,D2:S4	Expression	=AND(D$1>=$B2,D$1<=$C2)	text	NO

 

[ravi2628]

hi

if your schedule is on weekend then the logic needs to change.

so based on that a new criteria has been added.

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Work Site	Start Date	End Date	30-Sep-21	01-Oct-21	02-Oct-21	03-Oct-21	04-Oct-21	05-Oct-21	06-Oct-21	07-Oct-21	08-Oct-21	09-Oct-21	10-Oct-21	11-Oct-21	12-Oct-21	13-Oct-21	14-Oct-21	15-Oct-21
2	W1	02-Oct-21	03-Oct-21
3	W2	03-Oct-21	06-Oct-21
4	W3	12-Oct-21	13-Oct-21
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:C4	Expression	=OR(TEXT($C2,"DDD")="Sat",TEXT($C2,"DDD")="Sun")	text	NO
B2:B4	Expression	=OR(TEXT($B2,"DDD")="Sat",TEXT($B2,"DDD")="Sun")	text	NO
D2:S4	Expression	=OR(TEXT(D$1,"DDD")="Sat",TEXT(D$1,"DDD")="Sun")	text	YES
D2:S4	Expression	=AND(D$1>=$B2,D$1<=$C2)	text	NO

 

[Fluff]

Hi & welcome to MrExcel.

How can I construct a conditional formatting formula that will highlight cells in worksheet1 to show the days on which a worksite was being inspected based on the data in worksheet2?

Not sure you can do that with your version of Excel. I think that 2010 was the first version that allowed conditional formatting over different sheets.

 

[ladygermain]

Hi & welcome to MrExcel.

Not sure you can do that with your version of Excel. I think that 2010 was the first version that allowed conditional formatting over different sheets.

I just doubled checked and it looks like I actually have the 2016 version - whoops!

 

[ladygermain]

Hi May be this will be very use full and easy to get...

Thank you!

This has gotten me a lot closer but I'm running into an issue if I have more than one inspection listed for a worksite. The formatting will highlight the dates of the first inspection but won't also highlight any subsequent inspections for that worksite in the same row. Do you know how to fix this?

 

[ravi2628]

Thank you!

This has gotten me a lot closer but I'm running into an issue if I have more than one inspection listed for a worksite. The formatting will highlight the dates of the first inspection but won't also highlight any subsequent inspections for that worksite in the same row. Do you know how to fix this?

hi may be this will help.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Work Site	Start Date	End Date	Second Start Date	Second End Date	30-Sep-2021	01-Oct-2021	02-Oct-2021	03-Oct-2021	04-Oct-2021	05-Oct-2021	06-Oct-2021	07-Oct-2021	08-Oct-2021	09-Oct-2021	10-Oct-2021	11-Oct-2021	12-Oct-2021	13-Oct-2021	14-Oct-2021	15-Oct-2021
2	W1	02-Oct-2021	03-Oct-2021	4-Oct-21	4-Oct-21
3	W2	04-Oct-2021	06-Oct-2021
4	W3	12-Oct-2021	13-Oct-2021
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F2:U4	Expression	=AND(F$1>=$D2,F$1<=$E2)	text	NO
F2:U4	Expression	=AND(F$1>=$B2,F$1<=$C2)	text	NO

Cells with Data Validation
Cell	Allow	Criteria
D2:D4	Custom	and(B2<D4,C2>D4)
E2:E4	Custom	and(B2<E4,C2>E4)