Overlapping conditional formatting

[Sacruzsa]

Hello everyone,

I have a problem with our staff calendar, in Belgium (Dutch part and French part) the school holidays are different from the new school year, and since I work in a bilingual unit, both holidays have to be placed in the agenda, so it is now starting to look a bit like a coloring book.
Now I was wondering if it was possible to give overlapping conditional formats a different color or shading of the 2 colors used, just a matter of keeping it clean

As you can see I use green for the Dutch holidays and a kind of pink for the French holidays, these overlap each other and I would like to give this a different color or shading of both colors, just a matter of keeping it clear

SitPers Staff 2023-2024.xlsm
	DM	DN	DO	DP	DQ	DR	DS	DT	DU	DV	DW	DX	DY	DZ	EA	EB	EC	ED	EE	EF	EG	EH
10	Saturday		Sunday		Monday		Tuesday		Wednesday		Thursday		Friday		Saturday		Sunday		Monday		Tuesday
11	18/02/2023		19/02/2023		20/02/23		21/02/23		22/02/23		23/02/23		24/02/23		25/02/2023		26/02/2023		27/02/23		28/02/23
12	AM	PM	AM	PM	AM	PM	AM	PM	AM	PM	AM	PM	AM	PM	AM	PM	AM	PM	AM	PM	AM	PM
13
14
15
16
17
18
19
20
21
22
23
24
25
26
Sitpers 2023

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
U13:ABV26	Cell Value	contains "DO"	text	YES
U13:ABV26	Cell Value	contains "HAN"	text	YES
U13:ABV26	Cell Value	contains "ATG"	text	YES
U13:ABV26	Cell Value	contains "AMV"	text	YES
U13:ABV26	Cell Value	contains "SHU"	text	YES
U13:ABV26	Cell Value	contains "U2"	text	YES
U13:ABV26	Cell Value	contains "U1"	text	YES
U13:ABV26	Cell Value	contains "PRG"	text	YES
U13:ABV26	Cell Value	contains "OPS"	text	YES
U13:ABV26	Cell Value	contains "M"	text	YES
U13:ABV26	Cell Value	contains "FMN"	text	YES
U13:ABV26	Cell Value	contains "SP"	text	YES
U13:ABV26	Cell Value	contains "PRM"	text	YES
U13:ABV26	Cell Value	contains "Z"	text	YES
U13:ABV26	Cell Value	contains "SW"	text	YES
U13:ABV26	Cell Value	contains "HW"	text	YES
U13:ABV26	Cell Value	contains "SPE"	text	YES
U13:ABV26	Cell Value	contains "RP"	text	YES
U13:ABV26	Cell Value	contains "TP"	text	YES
U13:ABV26	Cell Value	contains "R"	text	YES
U13:ABV26	Cell Value	contains "V"	text	YES
U13:ABV26	Cell Value	contains "A"	text	YES
U13:ABV26	Cell Value	contains "AGR"	text	YES
U13:ABV26	Cell Value	contains "TBC"	text	YES
U13:ABV26	Cell Value	contains "Q"	text	YES
U13:ABV26	Cell Value	contains "P"	text	YES
U10:ABV26	Expression	=WEEKDAG(ZOEKEN(9^9;$U$11:U$11);2)>5	text	NO
U10:ABV26	Expression	=SOMPRODUCT((T$11>=Holiday!$B$18:$B$22)*(T$11<=Holiday!$C$18:$C$22))	text	YES
U10:ABV26	Expression	=SOMPRODUCT((U$11>=Holiday!$B$18:$B$22)*(U$11<=Holiday!$C$18:$C$22))	text	YES
U10:ABV26	Expression	=VERGELIJKEN(T$11;Holiday!$B$2:$B$13;0)	text	YES
U10:ABV26	Expression	=VERGELIJKEN(U$11;Holiday!$B$2:$B$13;0)	text	YES
U10:ABV26	Expression	=SOMPRODUCT((S$11>=Holiday!$B$18:$B$22)*(S$11<=Holiday!$F$18:$F$22))	text	YES
U10:ABV26	Expression	=SOMPRODUCT((R$11>=Holiday!$B$18:$B$22)*(R$11<=Holiday!$E$18:$F$22))	text	YES

Cells with Data Validation
Cell	Allow	Criteria
DM13:EH26	List	=$R$28:$R$53

SitPers Staff 2023-2024.xlsm
	A	B	C	D	E	F
17	Schoolvakantie NL	Begindatum	Einddatum	Schoolvakantie FR	Begindatum	Einddatum
18	Herfstvakantie	31/10/2023	6/11/2023	Herfstvakantie	23/10/2023	5/11/2023
19	Kerstvakantie	26/12/2022	8/01/2023	Kerstvakantie	25/12/2022	7/01/2023
20	Krokusvakantie	20/02/2023	26/02/2023	Krokusvakantie	20/02/2023	5/03/2023
21	Paasvakantie	3/04/2023	16/04/2023	Paasvakantie	29/04/2023	12/05/2023
22	Zomervakantie	30/06/2023	31/08/2023	Zomervakantie	6/07/2023	26/08/2023
Holiday

Thanks for the help

 

[bebo021999]

I am using 2 COUNTIFS to check if date fell into both holidays: COUNTIFS + COUNTIFS >1
NOTE: I am using LOOKUP to refer to nearest-left-cell in a merge area
LOOKUP(2,1/($U$11:U$11<>"")

Sample-CC.xlsx
	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP
10	Saturday		Sunday		Monday		Tuesday		Wednesday		Thursday		Friday		Saturday		Sunday		Monday		Tuesday
11	18/02/2023		19/02/2023		20/02/2023		21/02/2023		22/02/2023		23/02/2023		24/02/2023		25/02/2023		26/02/2023		27/02/2023		28/02/2023
12	AM	PM	AM	PM	AM	PM	AM	PM	AM	PM	AM	PM	AM	PM	AM	PM	AM	PM	AM	PM	AM	PM
13
14
15
16
17
18
19
20
21
22
23
24
25
26
Sitpers 2023

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
U13:AP26	Expression	=COUNTIFS(Holiday!$B$18:$B$22,"<="&LOOKUP(2,1/($U$11:U$11<>""),$U$11:U$11),Holiday!$C$18:$C$22,">="&LOOKUP(2,1/($U$11:U$11<>""),$U$11:U$11))+COUNTIFS(Holiday!$E$18:$E$22,"<="&LOOKUP(2,1/($U$11:U$11<>""),$U$11:U$11),Holiday!$F$18:$F$22,">=" & LOOKUP(2,1/($U$11:U$11<>""),$U$11:U$11))>1	text	NO

 

[Sacruzsa]

I am using 2 COUNTIFS to check if date fell into both holidays: COUNTIFS + COUNTIFS >1
NOTE: I am using LOOKUP to refer to nearest-left-cell in a merge area
LOOKUP(2,1/($U$11:U$11<>"")

Sample-CC.xlsx
	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP
10	Saturday		Sunday		Monday		Tuesday		Wednesday		Thursday		Friday		Saturday		Sunday		Monday		Tuesday
11	18/02/2023		19/02/2023		20/02/2023		21/02/2023		22/02/2023		23/02/2023		24/02/2023		25/02/2023		26/02/2023		27/02/2023		28/02/2023
12	AM	PM	AM	PM	AM	PM	AM	PM	AM	PM	AM	PM	AM	PM	AM	PM	AM	PM	AM	PM	AM	PM
13
14
15
16
17
18
19
20
21
22
23
24
25
26
Sitpers 2023

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
U13:AP26	Expression	=COUNTIFS(Holiday!$B$18:$B$22,"<="&LOOKUP(2,1/($U$11:U$11<>""),$U$11:U$11),Holiday!$C$18:$C$22,">="&LOOKUP(2,1/($U$11:U$11<>""),$U$11:U$11))+COUNTIFS(Holiday!$E$18:$E$22,"<="&LOOKUP(2,1/($U$11:U$11<>""),$U$11:U$11),Holiday!$F$18:$F$22,">=" & LOOKUP(2,1/($U$11:U$11<>""),$U$11:U$11))>1	text	NO

Hi, thanks for the response, but I can't get it to work, I keep getting errors

 

[bebo021999]

Try to test by: put that formula in cell U13 then copy to whole range. Where "TRUE" should be pink color range.
Sheet holiday must exists, as post #1

 

[Sacruzsa]

Try to test by: put that formula in cell U13 then copy to whole range. Where "TRUE" should be pink color range.
Sheet holiday must exists, as post #1

Ok , now the formula works but nothing happens , I changed the order but it has no effect , is there a possibility to upload the file ?

 

[shinigamilight]

=SUMPRODUCT(--(DM$11>=holidays!$B$18:$B$22)*--(DM$11<=holidays!$C$18:$C$22))+SUMPRODUCT(--(DM$11>=holidays!$E$18:$E$22)*--(DM$11<=holidays!$F$18:$F$22))=2

Place this at the top in conditional formatting and tick stopif true

 

[Sacruzsa]

=SUMPRODUCT(--(DM$11>=holidays!$B$18:$B$22)*--(DM$11<=holidays!$C$18:$C$22))+SUMPRODUCT(--(DM$11>=holidays!$E$18:$E$22)*--(DM$11<=holidays!$F$18:$F$22))=2

Place this at the top in conditional formatting and tick stopif true

i'm geting this error

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Overlapping conditional formatting
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[6StringJazzer]

i'm geting this error

The sheet name is Holiday not holidays. Correct the formula and maybe it will work.

 

[Sacruzsa]

The sheet name is Holiday not holidays. Correct the formula and maybe it will work.

Ok thanks in advance for this first aid now starting to get somewhere.
Code works now but now the problem is that only the left cell is colored, could this be because I merged cells?