Conditional Formatting

[Kobi Merrikin]

Hi Team,

I am creating a calendar view to show room bookings and have been able to pull through the start and end time of the event, I now just want to show the cells in between as highlighted. As you can see below, the Investor Event starts at 8 and finishes at 17:30, I would like the cells in between to be shown as highlighted as well.

The data is pulling through from a table on another tab

Thanks so much for you help in advance!

 

[JamesCanale]

If you are running 365, this should do the trick. It will throw an error if there is any double booking. Which is probably the right thing to do. But you can use textjoin to show both overlapping.

MrExcelPlayground23.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Name	Date	Start	End		Time	Monday, July 1, 2024	Tuesday, July 2, 2024	Wednesday, July 3, 2024	Thursday, July 4, 2024	Friday, July 5, 2024
2	Event 1	7/2/2024	8:00	8:30		8:00		Event 1
3	Event 2	7/2/2024	10:00	16:00		8:30
4	Event 3	7/3/2024	9:00	15:00		9:00			Event 3
5	Event 4	7/5/2024	11:00	13:00		9:30			Event 3
6						10:00		Event 2	Event 3
7						10:30		Event 2	Event 3
8						11:00		Event 2	Event 3		Event 4
9						11:30		Event 2	Event 3		Event 4
10						12:00		Event 2	Event 3		Event 4
11						12:30		Event 2	Event 3		Event 4
12						13:00		Event 2	Event 3
13						13:30		Event 2	Event 3
14						14:00		Event 2	Event 3
15						14:30		Event 2	Event 3
16						15:00		Event 2
17						15:30		Event 2
18						16:00
19						16:30
20						17:00
21						17:30
22						18:00
23						18:30
24						19:00
25						19:30
26						20:00
Sheet9

Cell Formulas
Range	Formula
H1:K1	H1	=G1+1
G2:K26	G2	=IFERROR(FILTER(Table7[[Name]:[Name]],(Table7[[Date]:[Date]]=G$1)*(Table7[[Start]:[Start]]<=$F2)*(Table7[[End]:[End]]>=$F2+0.5/24)),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G2:K26	Expression	=G2<>""	text	NO

 

[Joe4]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Peter_SSs]

I would like the cells in between to be shown as highlighted as well.

Is this what you mean?

24 07 06.xlsm
	A	B	C	D	E
1		Date1	Date2	Date3	Date4
2	Time1			Event1
3	Time2
4	Time3				Event2
5	Time4
6	Time5
7	Time6
8	Time7
9	Time8
10	Time9				Event2
11	Time10
12	Time11
13	Time12
14	Time13			Event1
15	Time14
16	Time15
17	Time16
18	Time17
19	Time18
20	Time19
CF Calendar Events

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:E20	Expression	=MATCH(LOOKUP("zzz",B$1:B2),B2:B$20,0)	text	NO

 

[Kobi Merrikin]

If you are running 365, this should do the trick. It will throw an error if there is any double booking. Which is probably the right thing to do. But you can use textjoin to show both overlapping.

MrExcelPlayground23.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Name	Date	Start	End		Time	Monday, July 1, 2024	Tuesday, July 2, 2024	Wednesday, July 3, 2024	Thursday, July 4, 2024	Friday, July 5, 2024
2	Event 1	7/2/2024	8:00	8:30		8:00		Event 1
3	Event 2	7/2/2024	10:00	16:00		8:30
4	Event 3	7/3/2024	9:00	15:00		9:00			Event 3
5	Event 4	7/5/2024	11:00	13:00		9:30			Event 3
6						10:00		Event 2	Event 3
7						10:30		Event 2	Event 3
8						11:00		Event 2	Event 3		Event 4
9						11:30		Event 2	Event 3		Event 4
10						12:00		Event 2	Event 3		Event 4
11						12:30		Event 2	Event 3		Event 4
12						13:00		Event 2	Event 3
13						13:30		Event 2	Event 3
14						14:00		Event 2	Event 3
15						14:30		Event 2	Event 3
16						15:00		Event 2
17						15:30		Event 2
18						16:00
19						16:30
20						17:00
21						17:30
22						18:00
23						18:30
24						19:00
25						19:30
26						20:00
Sheet9

Cell Formulas
Range	Formula
H1:K1	H1	=G1+1
G2:K26	G2	=IFERROR(FILTER(Table7[[Name]:[Name]],(Table7[[Date]:[Date]]=G$1)*(Table7[[Start]:[Start]]<=$F2)*(Table7[[End]:[End]]>=$F2+0.5/24)),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G2:K26	Expression	=G2<>""	text	NO

Thank you so much for this! This has worked perfectly

 

[Kobi Merrikin]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Thanks for this - I have done so now!