Conditional Formatting using cells filled by formula

[cking619]

Large spreadsheet with information filled in by formulas pulling from other spreadsheets which is also based on weeks. I have in the first column "A" the "week of" dates (i.e. "1/4/2021" in cell A3, "1/11/2021" in cell A4, etc.). Columns "I" through ""CD" separate out M-F with a Total at the end for each office (i.e B2 = Monday through F2 = Friday and G2 = Total to sum up the week). All of this occurs in 13 different sets of columns to account for 13 total offices.

Pic Example

What I would like is to conditionally format the cells that = 0 (based on zeros to total from the formulas populating each) AND are in the row with a date less than the next row to format a separate color. Hope this makes sense. As shown in my example, the "Atlanta" office has "0" in M-Total columns but the first 2 rows ("3 and 4") are past, so I would like those to change the color to separate from cells with "0" that have already occurred versus cells with "0" that haven't occurred yet.

I hope this makes sense and thank you, in advance, for any assistance.

[HansV]

Welcome to Eileen's Lounge!

Select I3:N10 (or however far down the data go).
I will assume that I3 is the active cell in the selection.
On the Home tab of the ribbon. select Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula

=AND(I3=0,$A3<TODAY())

Click Format...
Activate the Fill tab.
Select a highlight color.
Click OK, then click OK again.

You can use the Format Painter to copy the formatting to the ranges for the other offices.

[cking619]

Thank you, HansV!

Worked Perfect!