Excel 2021 conditional formatting that changes with the drop down menu

[BrerRabbit]

I have an excel "database" that does various calculations, data collecting and reports for an event that occurs every year. Various reports are generated from specific columns using the indirect function.

My question, with the below example, is when I chose 2023, the company names Target and Smith & Kline are conditionally highlighted bcuz those two are dated in the Date Paid 23 column. If I chose 2024 then Harods is conditionally highlighted bcuz that one is dated in teh Date Paid 24 column. And so forth.

I honestly have no clue as to how to do this in Excel.

 

[Peter_SSs]

Try this conditional formatting

BrerRabbit.xlsm
	A	B	C	D
1
2	2025
3
4
5	Co Name	Date paid 23	Date paid 24	Date paid 25
6	a	1/08/2023		26/07/2025
7	b		5/08/2024	4/08/2025
8	c	25/07/2023
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A6:A8	Expression	=FILTER(B6:D6,YEAR(B6:D6)=A$2)	text	NO

 

[BrerRabbit]

Try this conditional formatting

BrerRabbit.xlsm
	A	B	C	D
1
2	2025
3
4
5	Co Name	Date paid 23	Date paid 24	Date paid 25
6	a	1/08/2023		26/07/2025
7	b		5/08/2024	4/08/2025
8	c	25/07/2023
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A6:A8	Expression	=FILTER(B6:D6,YEAR(B6:D6)=A$2)	text	NO

I have to admit, I didn't think I'd get an answer to this one. You are a legend. And it WORKED!!

Unfortunately I have forgotten to account for the columns inbetween the Date Paid Columns, and then it doesn't work. I'm a dufus and I apologise. May I ask you to apply your genius one more time?

 

[Peter_SSs]

I have forgotten to account for the columns inbetween the Date Paid Column, and then it doesn't work.

By itself, having extra columns doesn't necessarily break it - see below.
Presumably there must be something in those other columns that stops it working. Can you tell/show us about that?

BrerRabbit.xlsm
	A	B	C	D	E	F	G
1
2	2023
3
4
5	Co Name	Other columns 23	Date paid 23	Other Columns 24	Date paid 24	Other Columns 25	Date paid 25
6	a		1/08/2023				26/07/2025
7	b				5/08/2024		4/08/2025
8	c		25/07/2023
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A6:B8	Expression	=FILTER(B6:G6,YEAR(B6:G6)=A$2)	text	NO

 

[BrerRabbit]

By itself, having extra columns doesn't necessarily break it - see below.
Presumably there must be something in those other columns that stops it working. Can you tell/show us about that?

BrerRabbit.xlsm
	A	B	C	D	E	F	G
1
2	2023
3
4
5	Co Name	Other columns 23	Date paid 23	Other Columns 24	Date paid 24	Other Columns 25	Date paid 25
6	a		1/08/2023				26/07/2025
7	b				5/08/2024		4/08/2025
8	c		25/07/2023
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A6:B8	Expression	=FILTER(B6:G6,YEAR(B6:G6)=A$2)	text	NO

you're right it doesn't, unless there are dates in the other columns. I didn't see the problem properly, I do confess. This is the actual problem.

 

[Peter_SSs]

unless there are dates in the other columns.

Yes, or text values. Try this one instead.

BrerRabbit.xlsm
	A	B	C	D	E	F	G
1
2	2024
3
4
5	Co Name	Other columns 23	Date paid 23	Date billed	Date paid 24	Other Columns 25	Date paid 25
6	a	27/04/1901	1/08/2023	27/04/1901		44000	26/07/2025
7	b			8/08/2024	5/08/2024	ggg	4/08/2025
8	c		25/07/2023	8/08/2024		-200
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A6:B8	Expression	=OR(YEAR(FILTER(B6:G6,LEFT(B$5:G$5,9)="Date paid"))=A$2)	text	NO

 

[BrerRabbit]

Yes, or text values. Try this one instead.

BrerRabbit.xlsm
	A	B	C	D	E	F	G
1
2	2024
3
4
5	Co Name	Other columns 23	Date paid 23	Date billed	Date paid 24	Other Columns 25	Date paid 25
6	a	27/04/1901	1/08/2023	27/04/1901		44000	26/07/2025
7	b			8/08/2024	5/08/2024	ggg	4/08/2025
8	c		25/07/2023	8/08/2024		-200
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A6:B8	Expression	=OR(YEAR(FILTER(B6:G6,LEFT(B$5:G$5,9)="Date paid"))=A$2)	text	NO

This works. GREAT. Thank you very very much. You Sir Are A Legend.

 

[Peter_SSs]

You are welcome. Glad to help. Thanks for the follow-up.