Conditional Formatting To Colour Cells That Contain No Data

I'm trying to figure out how to create conditional formatting for some cells in my workbook. I attached a sample of my situation.

Basically, I need any cells in any given row in columns F through O to display the following colours based on the following specifications:

Green, if the date at the end of the row in the Delivery Date column is more than 21 days from today's date.

Yellow, if the date at the end of the row in the Delivery Date column is less than 21 days and more than 7 days from today's date.

Red, if the date at the end of the row in the Delivery Date column is 7 days or less from today.

If any of the cells in any given row already contain data, the formatting does not apply to those cells only.

If there is no date in the Delivery Date, the formatting does not apply.

I've been struggling with this for a while now. Any help would be awesome!

assumption

there is no blank cell from B2 down in that columns

try this macro "cond_format"
I am sending the file after running the macro.
for retesting only macro "cond_format'
park these two macroS "test" and "undo" in a new module to avoid confusion(I have done it in module 3)

CHANGE NAME OF SHEET AS YOU LIKE
KEEP SHEET 1 AS ACTIVE SHEET AND RERUN "COND_FORMAT"
if necessary modify the code.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Thank you for your wonderful work, venkat1926.

After much work on this, I've settled with the solution as shown in the attachment. It seems to work best for my situation. Thanks so much!