INDIRECT(ADDRESS within SUMPRODUCT

[ImeoT]

Hi everyone,

I am trying to replace part of a formula to make it more dynamic.

To do this, I am replacing $B$10 with INDIRECT(ADDRESS(ROW(D15)-MOD(ROW(D15)+4,7),COLUMN(D15)-MOD(COLUMN(D15)-2,3)))

Now, ADDRESS(ROW(D15)-MOD(ROW(D15)+4,7),COLUMN(D15)-MOD(COLUMN(D15)-2,3)) = $B$10.

But replacing $B$10 in my formula does not work well for me.

Original formula:
=ISNUMBER(SEARCH("*text*",INDEX(Schedule!$F$2:$F$9998,SUMPRODUCT(($B$10=Schedule!$B$2:$B$9998)*($A$15=Schedule!$C$2:$C$9998)*($D$15=Schedule!$E$2:$E$9998)*(ROW(Schedule!$F$2:$F$9998)-1)),0)))

Original result:
TRUE

New Formula:
=ISNUMBER(SEARCH("*text*",INDEX(Schedule!$F$2:$F$9998,SUMPRODUCT((INDIRECT(ADDRESS(ROW(D15)-MOD(ROW(D15)+4,7),COLUMN(D15)-MOD(COLUMN(D15)-2,3)))=Schedule!$B$2:$B$9998)*($A$15=Schedule!$C$2:$C$9998)*($D$15=Schedule!$E$2:$E$9998)*(ROW(Schedule!$F$2:$F$9998)-1)),0)))

New Result:
FALSE

I would greatly appreciate any help with this, as I am pulling my hair out on this one!

 

[JustynaMK]

Thanks for explaining.


If I understand correctly, you would like to get something like this:

Excel 2013 32 bit
	A	B	C	D	E	F	G
1	13/11/2018
2	4:00 PM
3	Event A	07/11/2018	07/11/2018	07/11/2018	08/11/2018	08/11/2018	08/11/2018
4	4:00 AM	Event X	Event X	Event X	Event X	Event X	Event X
5	8:00 AM	Event X	Event X	Event X	Event X	Event X	Event X
6	12:00 PM	Event X	Event X	Event X	Event X	Event X	Event X
7	4:00 PM	Event X	Event X	Event X	Event X	Event X	Event X
8	8:00 PM	Event X	Event X	Event X	Event X	Event X	Event X
9	12:00 AM	Event X	Event X	Event X	Event X	Event X	Event X
10		12/11/2018	12/11/2018	12/11/2018	13/11/2018	13/11/2018	13/11/2018
11	4:00 AM				Event C	Event A	Event A
12	8:00 AM				Event C	Event A	Event A
13	12:00 PM				Event C	Event A	Event A
14	4:00 PM				Event A	Event B	Event C
15	8:00 PM				Event C	Event A	Event A
16	12:00 AM				Event C	Event A	Event A
Sheet1

Following this concept, if your whole "table" is presented in cells B3:P37, and your time slots are presented in cells A4:A37, then you can apply the following Conditional Formatting rules:


YELLOW fill color - will highlight the 7x3 rectangle which matches the input date present in cell A1.
Formula: =INDEX(B:B,ROUNDUP((ROW()-ROW(B$2))/7,0)*7-4,1)=$A$1
Applies to: =$B$3:$P$37


BLUE fill color - will highlight exactly one cell which matches the input date present in cell A1, time slot (cell A2) and Event name (cell A3).
Formula: =AND(INDEX(B:B,ROUNDUP((ROW()-ROW($A$2))/7,0)*7-ROW($A$4),1)=$A$1,$A3=$A$2,B3=$A$3)
Applies to: =$B$3:$P$37


However, please make sure that your BLUE Conditional Formatting rule is the first rule in the queue.

Let me know if this is what you're looking for.

 

[ImeoT]

Sorry for the delay getting back to you on this - my computer has been acting up.

Your explanation makes sense, but I need it integrated in a formula that also finds the date, time & event.

My raw data is in Worksheets("Schedule"):

	A	B	C	D	E	F	G	H	I	J	K	L
ID	Date	Time Start	Time End	Game	Attendees	Attendees #	Repeat	Min	Max	Length	Viability
Event A	Alex	No
Event B	Alex, Bob	OK
Event A	Bob, Chloe	OK
Event C	Chloe, Alex	OK

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5/11/2018[/TD]
[TD="align: right"]6:00 PM[/TD]
[TD="align: right"]6:30 PM[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]0h7m0s[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]5/11/2018[/TD]
[TD="align: right"]7:00 PM[/TD]
[TD="align: right"]8:00 PM[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1h0m0s[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5/11/2018[/TD]
[TD="align: right"]7:00 PM[/TD]
[TD="align: right"]8:00 PM[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]0h56m0s[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]9/11/2018[/TD]
[TD="align: right"]7:00 PM[/TD]
[TD="align: right"]7:30 PM[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0h30m0s[/TD]

</tbody>

Schedule​

I want to change the font for each cell in the following calendar, where Alex is an Attendee for the event taking place at the specified date and time.

Excel 2013/2016

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
MONDAY			TUESDAY			WEDNESDAY			THURSDAY			FRIDAY

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]November 2018[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6E6E6]#E6E6E6[/URL] "]29[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6E6E6]#E6E6E6[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6E6E6]#E6E6E6[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6E6E6]#E6E6E6[/URL] "]30[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6E6E6]#E6E6E6[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6E6E6]#E6E6E6[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6E6E6]#E6E6E6[/URL] "]31[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6E6E6]#E6E6E6[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6E6E6]#E6E6E6[/URL] "][/TD]
[TD="bgcolor: #DFECF2"]1[/TD]
[TD="bgcolor: #DFECF2"][/TD]
[TD="bgcolor: #DFECF2"][/TD]
[TD="bgcolor: #DFECF2"]2[/TD]
[TD="bgcolor: #DFECF2"][/TD]
[TD="bgcolor: #DFECF2"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]5:00 PM[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6E6E6]#E6E6E6[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6E6E6]#E6E6E6[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6E6E6]#E6E6E6[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6E6E6]#E6E6E6[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6E6E6]#E6E6E6[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6E6E6]#E6E6E6[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6E6E6]#E6E6E6[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6E6E6]#E6E6E6[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6E6E6]#E6E6E6[/URL] "][/TD]
[TD="bgcolor: #DFECF2"][/TD]
[TD="bgcolor: #DFECF2"][/TD]
[TD="bgcolor: #DFECF2"][/TD]
[TD="bgcolor: #DFECF2"][/TD]
[TD="bgcolor: #DFECF2"][/TD]
[TD="bgcolor: #DFECF2"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]5:30 PM[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6E6E6]#E6E6E6[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6E6E6]#E6E6E6[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6E6E6]#E6E6E6[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6E6E6]#E6E6E6[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6E6E6]#E6E6E6[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6E6E6]#E6E6E6[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6E6E6]#E6E6E6[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6E6E6]#E6E6E6[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6E6E6]#E6E6E6[/URL] "][/TD]
[TD="bgcolor: #DFECF2"][/TD]
[TD="bgcolor: #DFECF2"][/TD]
[TD="bgcolor: #DFECF2"][/TD]
[TD="bgcolor: #DFECF2"][/TD]
[TD="bgcolor: #DFECF2"][/TD]
[TD="bgcolor: #DFECF2"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]6:00 PM[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6E6E6]#E6E6E6[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6E6E6]#E6E6E6[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6E6E6]#E6E6E6[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6E6E6]#E6E6E6[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6E6E6]#E6E6E6[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6E6E6]#E6E6E6[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6E6E6]#E6E6E6[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6E6E6]#E6E6E6[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6E6E6]#E6E6E6[/URL] "][/TD]
[TD="bgcolor: #DFECF2"][/TD]
[TD="bgcolor: #DFECF2"][/TD]
[TD="bgcolor: #DFECF2"][/TD]
[TD="bgcolor: #DFECF2"][/TD]
[TD="bgcolor: #DFECF2"][/TD]
[TD="bgcolor: #DFECF2"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]6:30 PM[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6E6E6]#E6E6E6[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6E6E6]#E6E6E6[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6E6E6]#E6E6E6[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6E6E6]#E6E6E6[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6E6E6]#E6E6E6[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6E6E6]#E6E6E6[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6E6E6]#E6E6E6[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6E6E6]#E6E6E6[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6E6E6]#E6E6E6[/URL] "][/TD]
[TD="bgcolor: #DFECF2"][/TD]
[TD="bgcolor: #DFECF2"][/TD]
[TD="bgcolor: #DFECF2"][/TD]
[TD="bgcolor: #DFECF2"][/TD]
[TD="bgcolor: #DFECF2"][/TD]
[TD="bgcolor: #DFECF2"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]7:00 PM[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6E6E6]#E6E6E6[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6E6E6]#E6E6E6[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6E6E6]#E6E6E6[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6E6E6]#E6E6E6[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6E6E6]#E6E6E6[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6E6E6]#E6E6E6[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6E6E6]#E6E6E6[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6E6E6]#E6E6E6[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6E6E6]#E6E6E6[/URL] "][/TD]
[TD="bgcolor: #DFECF2"][/TD]
[TD="bgcolor: #DFECF2"][/TD]
[TD="bgcolor: #DFECF2"][/TD]
[TD="bgcolor: #DFECF2"][/TD]
[TD="bgcolor: #DFECF2"][/TD]
[TD="bgcolor: #DFECF2"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]7:30 PM[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6E6E6]#E6E6E6[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6E6E6]#E6E6E6[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6E6E6]#E6E6E6[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6E6E6]#E6E6E6[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6E6E6]#E6E6E6[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6E6E6]#E6E6E6[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6E6E6]#E6E6E6[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6E6E6]#E6E6E6[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6E6E6]#E6E6E6[/URL] "][/TD]
[TD="bgcolor: #DFECF2"][/TD]
[TD="bgcolor: #DFECF2"][/TD]
[TD="bgcolor: #DFECF2"][/TD]
[TD="bgcolor: #DFECF2"][/TD]
[TD="bgcolor: #DFECF2"][/TD]
[TD="bgcolor: #DFECF2"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #F6F6F1"]5[/TD]
[TD="bgcolor: #F6F6F1"][/TD]
[TD="bgcolor: #F6F6F1"][/TD]
[TD="bgcolor: #F6F6F1"]6[/TD]
[TD="bgcolor: #F6F6F1"][/TD]
[TD="bgcolor: #F6F6F1"][/TD]
[TD="bgcolor: #F6F6F1"]7[/TD]
[TD="bgcolor: #F6F6F1"][/TD]
[TD="bgcolor: #F6F6F1"][/TD]
[TD="bgcolor: #F6F6F1"]8[/TD]
[TD="bgcolor: #F6F6F1"][/TD]
[TD="bgcolor: #F6F6F1"][/TD]
[TD="bgcolor: #F6F6F1"]9[/TD]
[TD="bgcolor: #F6F6F1"][/TD]
[TD="bgcolor: #F6F6F1"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]5:00 PM[/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F6F6F1"][/TD]
[TD="bgcolor: #F6F6F1"][/TD]
[TD="bgcolor: #F6F6F1"][/TD]
[TD="bgcolor: #F6F6F1"][/TD]
[TD="bgcolor: #F6F6F1"][/TD]
[TD="bgcolor: #F6F6F1"][/TD]
[TD="bgcolor: #F6F6F1"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]5:30 PM[/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F6F6F1"][/TD]
[TD="bgcolor: #F6F6F1"][/TD]
[TD="bgcolor: #F6F6F1"][/TD]
[TD="bgcolor: #F6F6F1"][/TD]
[TD="bgcolor: #F6F6F1"][/TD]
[TD="bgcolor: #F6F6F1"][/TD]
[TD="bgcolor: #F6F6F1"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]6:00 PM[/TD]
[TD="bgcolor: #F2F2F2"]Event A[/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F6F6F1"][/TD]
[TD="bgcolor: #F6F6F1"][/TD]
[TD="bgcolor: #F6F6F1"][/TD]
[TD="bgcolor: #F6F6F1"][/TD]
[TD="bgcolor: #F6F6F1"][/TD]
[TD="bgcolor: #F6F6F1"][/TD]
[TD="bgcolor: #F6F6F1"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]6:30 PM[/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F6F6F1"][/TD]
[TD="bgcolor: #F6F6F1"][/TD]
[TD="bgcolor: #F6F6F1"][/TD]
[TD="bgcolor: #F6F6F1"][/TD]
[TD="bgcolor: #F6F6F1"][/TD]
[TD="bgcolor: #F6F6F1"][/TD]
[TD="bgcolor: #F6F6F1"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]7:00 PM[/TD]
[TD="bgcolor: #F2F2F2"]Event B[/TD]
[TD="bgcolor: #F2F2F2"]Event A[/TD]
[TD="bgcolor: #F2F2F2"]Event C[/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F6F6F1"][/TD]
[TD="bgcolor: #F6F6F1"][/TD]
[TD="bgcolor: #F6F6F1"][/TD]
[TD="bgcolor: #F6F6F1"][/TD]
[TD="bgcolor: #F6F6F1"][/TD]
[TD="bgcolor: #F6F6F1"][/TD]
[TD="bgcolor: #F6F6F1"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]7:30 PM[/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F6F6F1"][/TD]
[TD="bgcolor: #F6F6F1"][/TD]
[TD="bgcolor: #F6F6F1"][/TD]
[TD="bgcolor: #F6F6F1"][/TD]
[TD="bgcolor: #F6F6F1"][/TD]
[TD="bgcolor: #F6F6F1"][/TD]
[TD="bgcolor: #F6F6F1"][/TD]

</tbody>

November 2018
​

As mentioned before, there is some issue with the formula:
=ISNUMBER(SEARCH("*Alex*",INDEX(Schedule!$F$2:$F$999,SUMPRODUCT((INDIRECT(ADDRESS(ROWS(B$1:B3)-MOD(ROWS(B$1:B3)+4,7),COLUMNS($A3:B3)-MOD(COLUMNS($A3:B3)-2,3)))=Schedule!$B$2:$B$999)*($A3=Schedule!$C$2:$C$999)*(B3=Schedule!$E$2:$E$999)*(ROW(Schedule!$F$2:$F$999)-1)),0)))

Excel 2013/2016

	B*	C*	D*	E*	F*	G*	H*	I*	J*

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]3[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]

</tbody>

November 2018​

It also dawned on me that, using a shared workbook, my VBA code won't be able to add new conditional formatting, so I will need to rely on similar catch-all formatting for some similar testing of the event viability. All I need is to get this first formatting right first!