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

Question

Hello!

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!

Answer 1

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

---

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.

---

Answer 2

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

Home

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.