Conditional format cells where deadlines have passed

[Fastpat27]

Hi all
I have a list of deadlines for different reports (top row), and based on a date the staff enter as sent on a different tab, I have managed to show if they were early, on time or late. Everything shows as waiting in purple until their date is entered. I however would like the waiting cells to turn red if the deadline passes today's date (04/11/22 - cell Z1). I can do this in one cell at a time with formula =$B4<$Z$1 but how do I apply it to all cells easily?

The Auto Cells are reports that I receive automatically from a back office system
Thanks
FP

 

[Joe4]

So, if you wanted to apply this new Conditional Formatting area to the cells B5:Y9, do the following:
1. Select the range B5:Y9
2. Go to Conditional Formatting
3. Select "New Rule"
4. Choose the "Use a formula to determine which cells to format" option
5. Enter the following formula:

=AND(B$4<TODAY(),B5="Waiting")

6. Choose your red formatting option
7. Click "OK"

I think that should do what you want.

 

[Fastpat27]

Great work Joe4, worked a treat, thank you so much

 

[Joe4]

You are welcome!
Glad I was able to help.