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

 

[SaraWitch]

Sorry, B4 should be red too...

 

[skillilea]

What is your should be column?
Are you entering only in column A or anywhere?

Explain a little more about the use case.

 

[SaraWitch]

Any new address could be entered anywhere in the range A2:Y10. However, if that address is active anywhere else in the same range (i.e., it has been entered elsewhere but has no end date ("Date to" in columns E, I, M, Q, U, Y)), then change both cells red.

The "Should be" column in my example is the colour I would like the cell to be (with B4 also being red).

Apologies, and hope this makes it a little clearer.

 

[SaraWitch]

Here's more example data, if it helps:

¦ 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	Z
1	Address 1	Should be	Location	Date from	Date to	Address 2	Should be	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		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	Sky Apartments, Stormville	East Sun			Low Heaven, Angel Cove	East Sun	06/01/2022		Ocean Whirlpool, Seagate	East Sun	10/10/2023	10/11/2023
5	Gnome Cave, High Cloud	Gnome Cave, High Cloud				Forest Mound, Woodland	Forest Mound Woodland
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
CF Active Same

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A10,F2:F10	Expression	=AND(ISNUMBER(MATCH(A2,A2:A10,0)),ISBLANK(E2:E10))	text	NO
W2:W10	Expression	=AND(ISNUMBER(MATCH(W2,W2:W10,0)),ISBLANK(Z2:Z10))	text	NO
K2:K10,O2:O10,S2:S10	Expression	=AND(ISNUMBER(MATCH(K2,K2:K10,0)),ISBLANK(N2:N10))	text	NO

 

[skillilea]

I'm not sure how many addresses you have to the right.

Anyway to store the data this way?
Can you use a simple macro to loop through used range of cells and check?

Book1
	A	B	C	D	E
1	Address #	Address	Location	Date from	Date to
2	1	Gnome Cave, High Cloud	East Sun	8/25/2022	8/7/2022
3	2	Gnome Cave, High Cloud	East Sun	8/25/2022	8/7/2022
4	3	Gnome Cave, High Cloud	East Sun	8/25/2022
5	4	Gnome Cave, High Cloud
6	1	Sky Apartments, Stormville
7	1	Low Heaven, Angel Cove
8	1	Ocean Whirlpool, Seagate
9	1	Valley Deep, Grasslands
Sheet3

 

[SaraWitch]

Thank you, @*skillilea. There are six addresses (you should be able to scroll across to column Z in the examples).

I'm not quite sure what you mean by using a simple macro to loop through.

I want a cell to change colour if the address being entered is already active elsewhere in the sheet (i.e., if the address is elsewhere and it does not have a date entered in the 'Date to' column immediately after). If the address is elsewhere in the sheet and does have a date entered in the 'Date to' column, then the cell does not need to change colour. And the rows cannot be moved around (they need to stay in the same order as in my examples).

Not sure it can be done...?

 

[skillilea]

Hang on a bit it can

 

[skillilea]

Are you only entering in Address1 or can you enter in any of the address columns?

 

[SaraWitch]

Any of the address columns...