I have 4 conditional formats attched to column D, all of which are dependent on the date indicated on the first column cell and the status in column B, which auto-updates based on results from sheet2 columnA.

Example: Cell D4 has 4 optional conditional formats that change color if the date is <= that on cell D1 and the status on cell B4, (note cell B4 in turn continuously updates based on Sheet 2 cell A4). So I would like format cell D4 to keep the color it has on date D1 when the day has passed, but continue updating the color on the subsequent cell E4, until the deadline date on E1, when it retains the status and continue to update next cell F4 and so on. Attached is an excel example.

Will grately appreciate any insight.

Mwash