SOLVED

# Cell color changes when registration date nearing expiry?

Occasional Contributor

# Cell color changes when registration date nearing expiry?

I am creating a spreadsheet for vehicle registration and I would like the cell color to change when it is nearing expiry. Is this possible?  Thanks in advance

- Cell color RED if date in cell is today's date or older

- Cell color ORANGE if date in cell is between tomorrow's date and 1 months time

- Cell color GREEN if date in cell is more than 1 month from today

12 Replies

# Re: Cell color changes when registration date nearing expiry?

@user1234321 You need 3 conditional formatting rule. See attached file.

`For red =A2<=TODAY()For orange =AND(A2-TODAY()>=1,A2-TODAY()<=31)For green =A2-TODAY()>31`

# Re: Cell color changes when registration date nearing expiry?

How To Conditional Formatting Based On Date In Excel?

• equal to today:=\$B2=TODAY()
• reater than today: =\$B2>TODAY()
• Older than today: =\$B2<TODAY()

NikolinoDE

I know I don't know anything (Socrates)

best response confirmed by user1234321 (Occasional Contributor)
Solution

# Re: Cell color changes when registration date nearing expiry?

Thankyou for this, much appreciated.
Can I duplicate these rules to other cells without manually changing A2 to the new cell, eg A3?

# Re: Cell color changes when registration date nearing expiry?

You do not need to duplicate the rules. It will automatically apply to all cells given to Applies To range.

# Re: Cell color changes when registration date nearing expiry?

I am working with Google sheets - when I add the custom formula to Conditional format rules it applies A2 only

# Re: Cell color changes when registration date nearing expiry?

Your image would suggest that you have been successful in applying the conditional format to cells A2 and A17. I would, however, suggest that you are sparing with your use of colour. It is difficult to read text against a dark colour and overuse reduces its impact.

# Re: Cell color changes when registration date nearing expiry?

It does change colour however is using A2 value, not A3, A4 etc. All cells end up being the same colour

# Re: Cell color changes when registration date nearing expiry?

The image shows A1,A17. That is A1 and A17, not A1 to A17. The range is written A1:A17.
I hope that helps.

# Re: Cell color changes when registration date nearing expiry?

It does help, thank you for that.
I have a date that should be orange but instead it is white. Any idea's why the orange is not taking effect?

# Re: Cell color changes when registration date nearing expiry?

Most likely the green CF has worked but there is still an error in the red or yellow.

# Re: Cell color changes when registration date nearing expiry?

@Harun24HR
The green is working thank you, however if it meets the value of Orange it changes to white

# Re: Cell color changes when registration date nearing expiry?

Please disregard - I re-entered the formulas and it is working. Thanks so much everyone