Conditional formatting based on dates

[DarkoDeign2]

Hi,

In column: W, Y, AA, AC & AE, I have dates. Some columns/cells are empty.
The range of these columns are from 2 to 1500, so e.g. W2:W1500, AA2:AA1500 and so on...
If there are dates in the colums, the first date is in col W. In the next col Z the date would be the value from W + 7days and so on.
W = (a random date)
Y = W+7days
AA = W+Y+7days
AC = W+Y+AA+7days
AE = W+Y+AA+AC+7days

I am looking for a solution for conditional formatting on every row.
To highlight the last date and fill the cell with lets say green color if the date in this cell is older than todays date.

Book1
	W		Y		AA		AC		AE
1	2023-06-20		2023-06-27		2023-07-04		2023-07-11		2023-07-18
2	2023-07-02		2023-07-09		2023-07-16
3	2023-07-04
4	2023-07-07		2023-07-14		2023-07-21		2023-07-28
5	2023-07-07		2023-07-14		2023-07-21		2023-07-28
6	2023-07-07		2023-07-14		2023-07-21		2023-07-28
7	2023-07-11		2023-07-18		2023-07-25		2023-08-01
8	2023-07-14		2023-07-21		2023-07-28		2023-08-04
9	2023-07-17		2023-07-24		2023-07-31
10	2023-07-17		2023-07-24		2023-07-31
11	2023-07-18
12	2023-07-20		2023-07-27
13	2023-07-21		2023-07-28
14	2023-07-21		2023-07-28
Sheet1

 

[JamesCanale]

MrExcelPlayground19.xlsx
	W	X	Y	Z	AA	AB	AC	AD	AE
1	6/20/2023		6/27/2023		7/4/2023		7/11/2023		7/18/2023
2	7/2/2023		7/9/2023		7/16/2023
3	7/4/2023
4	7/7/2023		7/14/2023		7/21/2023		7/28/2023
5	7/7/2023		7/14/2023		7/21/2023		7/28/2023
6	7/7/2023		7/14/2023		7/21/2023		7/28/2023
7	7/11/2023		7/18/2023		7/25/2023		8/1/2023
8	7/14/2023		7/21/2023		7/28/2023		8/4/2023
9	7/17/2023		7/24/2023		7/31/2023
10	7/17/2023		7/24/2023		7/31/2023
11	7/18/2023
12	7/20/2023		7/27/2023
13	7/21/2023		7/28/2023
14	7/21/2023		7/28/2023
Sheet8

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
W1:AE14	Expression	=AND(W1=MAX($W1:$AE1),W1<TODAY())	text	NO
W1:AE14	Expression	=W1=MAX($W1:$AE1)	text	NO

 

[DarkoDeign2]

Hi,

Somehow I don't get this to work. Random cells are highlighted.
I selected W-AE columns at once and applied the conditional formatting. Should I have done it differently?

 

[JamesCanale]

I converted mine to work on the whole columns, and it works... Does the 'randomness' look like it's just offset by one row?

 

[DarkoDeign2]

Hi again,
Now it works, I realised that the formula start from W1, but my conditional formatting starts from W2.
So now it works perfectly.
But the latter parts of the colums that are completly empty turns yellow...
Thanks for the quick help.

 

[JamesCanale]

This should sort the empty parts:

MrExcelPlayground19.xlsx
	W	X	Y	Z	AA	AB	AC	AD	AE
1	6/20/2023		6/27/2023		7/4/2023		7/11/2023		7/18/2023
2	7/2/2023		7/9/2023		7/16/2023
3	7/4/2023
4	7/7/2023		7/14/2023		7/21/2023		7/28/2023
5	7/7/2023		7/14/2023		7/21/2023		7/28/2023
6	7/7/2023		7/14/2023		7/21/2023		7/28/2023
7	7/11/2023		7/18/2023		7/25/2023		8/1/2023
8	7/14/2023		7/21/2023		7/28/2023		8/4/2023
9	7/17/2023		7/24/2023		7/31/2023
10	7/17/2023		7/24/2023		7/31/2023
11	7/18/2023
12	7/20/2023		7/27/2023
13	7/21/2023		7/28/2023
14	7/21/2023		7/28/2023
15
16
17
18
19
Sheet8

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
W:AE	Expression	=AND(W1=MAX($W1:$AE1),W1<TODAY(),W1<>"")	text	NO
W:AE	Expression	=AND(W1<>"",W1=MAX($W1:$AE1))	text	NO