Highlight cell if text matches in range of cells, but only if other cells are blank

[SaraWitch]

Hello peeps,

I have a range of cells that contain addresses with start and end dates. I want to highlight a cell if a new address is entered which is already 'active' elsewhere on the sheet; i.e., address is elsewhere and hasn't got an end date. I'm tying myself up in knots a bit!

¦ MrExcel Queries.xlsm
	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
1	Address 1	Should be	Location	Date from	Date to	Address 2	Location	Date from	Date to	Address 3	Location	Date from	Date to	Address 4	Location	Date from	Date to	Address 5	Location	Date from	Date to	Address 6	Location	Date from	Date to
2	Gnome Cave, High Cloud		East Sun	25/08/2022	07/08/2022	Sky Apartments, Stormville	East Sun	08/08/2022	05/01/2022	Low Heaven, Angel Cove	East Sun	06/01/2022	09/10/2023	Ocean Whirlpool, Seagate	East Sun	10/10/2023	10/11/2023	Valley Deep, Grasslands	East Sun	11/11/2023	01/01/2024	Forest Mound, Woodland		02/01/2024	03/01/2024
3	Gnome Cave, High Cloud		East Sun	25/08/2022	07/08/2022	Sky Apartments, Stormville	East Sun	08/08/2022	05/01/2022	Low Heaven, Angel Cove	East Sun	06/01/2022	09/10/2023	Ocean Whirlpool, Seagate	East Sun	10/10/2023	10/11/2023	Valley Deep, Grasslands	East Sun	11/11/2023
4	Gnome Cave, High Cloud	Gnome Cave, High Cloud	East Sun	25/08/2022		Sky Apartments, Stormville	East Sun	08/08/2022	05/01/2022	Low Heaven, Angel Cove	East Sun	06/01/2022		Ocean Whirlpool, Seagate	East Sun	10/10/2023	10/11/2023		East Sun
5	Gnome Cave, High Cloud	Gnome Cave, High Cloud
6	Sky Apartments, Stormville	Sky Apartments, Stormville
7	Low Heaven, Angel Cove	Low Heaven, Angel Cove
8	Ocean Whirlpool, Seagate	Ocean Whirlpool, Seagate
9	Valley Deep, Grasslands	Valley Deep, Grasslands
10	Forest Mound, Woodland	Forest Mound, Woodland
CF Active Same

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B9	Expression	=AND(ISNUMBER(MATCH(B9,B9:B17,0)),ISBLANK(F9:F17))	text	NO
B7	Expression	=AND(ISNUMBER(MATCH(B7,B7:B15,0)),ISBLANK(F7:F15))	text	NO
B4	Expression	=AND(ISNUMBER(MATCH(B4,B4:B12,0)),ISBLANK(F4:F12))	text	NO
B5,A2:A10	Expression	=AND(ISNUMBER(MATCH(A2,A2:A10,0)),ISBLANK(E2:E10))	text	NO
V2:V10	Expression	=AND(ISNUMBER(MATCH(V2,V2:V10,0)),ISBLANK(Y2:Y10))	text	NO
F2:F10,J2:J10,N2:N10,R2:R10	Expression	=AND(ISNUMBER(MATCH(F2,F2:F10,0)),ISBLANK(I2:I10))	text	NO

Any help would be appreciated

 

[skillilea]

Sorry SaraWitch I played with it for a bit but, it's multiple(s) of CF's.
I got it semi working with code using a dictionary.
Not sure I'd even pursue that approach as it's clunky.
I'm hoping for some brilliance from the board to help your out.

 

[SaraWitch]

Thank you for trying @skillilea

Anyone else know of a solution please?