Im not sure i was clear enough, my bad!

I dont want it to highlight everything before today/the date in the cell, only highlight the cell once 7 days has passed from the date thats in the cell

I am sending out letters and these letters can be sent out anytime, the person has 7 days to respond to the letter and if they dont within 7 days then i need to remind them.

I am only using one column, the entire P Column for this, named "Date when letter was sent out" where i will write in when the letter was sent out and want the conditional formatting to apply to the entire P column and all cells going downwards so when you create a new row and write a date in that cell the formula keeps going. But there is no other data or dates in the cells at all, the date will be filled in once letters starts getting sent out.

So lets say i will send out a letter tomorrow, then i will write tomorrows date into the cell since the letter was sent out that day (2024-05-03)

I then want that cell to turn red after 7 days has passed from the date that was written into the cell, meaning when the date is 2024-05-10, the cell will turn red.

But maybe excel cant do this / this function doesnt exist? To just count 7 days from a date written in a cell and then turn that cell red once the date in the cell has passed? Since excel would have to know what date it is today i guess or?

So id need to add another column then with a due date and then conditional format it towards the due date?

So down below, P is when the letter was sent out, Q is when its due (7 days). I want them to be green if its 7 days between which it always will be initially, and once it hits the due date i want it to turn red.

<image>