I have a table in which the headers are dates, and the values in the data body are the planned shipped quantities.

so the cell should go red if there are values in the cell and the header date is after today.

I put the formula into the conditional formatting section but when I use format painter it keeps the references absolute. 


=AND(AG$7>=TODAY(),AG8<>"")