How to create a conditional Format that changes colour the closer it gets to a deadline?

Lo's Pharmacy 20 Reputation points


I am trying to create a conditional format that changes colour 3 times , to a deeper shade each time the real time date changes, and is closer to the dead line, the deepest shade being the day of.

lightest shade >7 days to complete

middle shade 3 days to complete

The formatting needs to apply to all cells in the row.

If anyone could give me a step by step I would greatly appreciate that!

User's image

A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
1,612 questions
0 comments No comments
{count} votes

Accepted answer
  1. Jiajing Hua-MFST 6,745 Reputation points Microsoft Vendor

    Hi @Lo's Pharmacy

    Thanks for your kind sharing.

    You may also use conditional formatting formula.

    • Select all the cells that you want to apply the conditional formatting.
    • Click Conditional Formatting > New Rule > Use a formula to determine which cells to format > Enter following formulas and set the corresponding fill color. (Here, the Deadline in B column)

    =$B2-TODAY()<=3 ---> middle shade 3 days to complete =$B2-TODAY()>7 ---> lightest shade >7 days to complete

    enter image description here

    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
  1. Lo's Pharmacy 20 Reputation points

    I Figured it out, but to those wondering, here is how:


    Conditional formatting

    Highlight cells rules

    A date occurring....

    Pick the time period for the deepest shade, then pick the shade.

    Repeat for the other two with the relevant times and shades.

    To apply to a row.

    Conditional formatting

    Manage rules

    Then select the rule , and the 'Applies to' Section, then just click all the cells / the row you would like to apply the role to.

    Repeat for all shades.

    Hope this is helpful to those struggling.

    Faye Taylor,

    Receptionist at SKF LO Chemist and Pharmacy.

    0 comments No comments