Formula For Conditional Formatting

[JEF13]

We use Excel to track appointments for a podcast.
I'd like to create a conditional formula that highlights a cell in red if the date of publication of the podcast is before the person records an interview.

The sheet is divided into two areas. The top of the sheet shows us when each episode airs and the name of the guest. The columns are as follows:

Guest Name: A2 to A106
Date for when the episode airs: B2 to B106

The bottom of the sheet is when we book the actual podcast interview. The columns are as follows:

Date of interview: A114 to A217
Name of guest: B114 to B217

The formatting would do the following:

1. For the name in cell A2 AND the date in cell B2 find the same name in cells B114 to B217
2. Compare the date in cell B2 with the date for the same name in A114 to A217
3. If the date in cell B2 is AFTER the date in cells A116 to A217 than nothing happens
4. If the date in cell B2 is BEFIRE the date in cells A116 to A217 than the cell turns red


Thanks for your help!

 

[Eric W]

Try:

Book1
	A	B
1	Name	Date
2	Bob	2/1/2025
3	Andy	4/1/2025
4	Esme	1/1/2025
5	Chantel	1/2/2025
6
7
8
113	Name	Date
114	Andy	3/2/2025
115	Bob	2/28/2025
116	Chantel	1/2/2025
117	Esme	1/31/2025
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B5	Expression	=B2<VLOOKUP(A2,$A$114:$B$217,2,0)	text	NO

If you want it to highlight if the date is equal, change the < to <=.

 

[etaf]

Maybe something like

=countifs($A$114:$A$217,$A2,$B$114:$B$217,"<"&$B2)>0

for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>
A2 to B106- Change, reduce or extend the rows to meet your data range of rows

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:
=countifs($A$114:$A$217,$A2,$B$114:$B$217,"<"&$B2)>0

Format [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK

 

[JEF13]

Try:

Book1
	A	B
1	Name	Date
2	Bob	2/1/2025
3	Andy	4/1/2025
4	Esme	1/1/2025
5	Chantel	1/2/2025
6
7
8
113	Name	Date
114	Andy	3/2/2025
115	Bob	2/28/2025
116	Chantel	1/2/2025
117	Esme	1/31/2025
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B5	Expression	=B2<VLOOKUP(A2,$A$114:$B$217,2,0)	text	NO

If you want it to highlight if the date is equal, change the < to <=.

Perfect, worked as intended. Thanks Eric!

 

[Eric W]

Glad we could help!