How To Make Excel Change Color Based On Date

Respond

Hello,

you need four conditional formats, i for each color. Select the cell. In the screenshot I selected prison cell A2. The formula will use the currently selected cell.

Create a conditional format with a formula, using

=A2-TODAY()>=xxx

Format to blue. Note that there are no $ signs in the cell reference. A2 is the current cell and using no $ signs in the formula will expect at the current prison cell when you employ the format to other cells.

Create three more than formats with these formulas:

=A2-TODAY()<=29 -- format to yellowish

=A2-TODAY()<=14 -- format to orange

=A2-TODAY()<=5 -- format to red.

In the Rules Manager the rules should be in this club:

Y'all tin can use the "Applies To" fields in this dialog to select the range for which the format should be applied, or you lot can copy the formatted cell and employ Paste Special > Formats to copy the provisional format to other cells.

Permit me know if that works for yous.

___________________
cheers, teylyn

Community Moderator
www.teylyn.com

48 people found this reply helpful

Was this reply helpful?

Lamentable this didn't help.

Bully! Thanks for your feedback.

How satisfied are you with this reply?

Cheers for your feedback, it helps us amend the site.

How satisfied are you with this reply?

Thanks for your feedback.

Hi,

Attempt this

1. Suppose Date of show is in cell A2 and Today'due south date is in cell B2

ii. In cell C2, enter this formula =A2-B2

three. Click on jail cell C2 and get to Habitation > Conditional formatting > New Rule > Use a formula to determine which cells to format

4. In the Formula bar in that location, enter this formula

=C2<=v

5. Click on Format > Fill up > Red > OK > Apply

6. Echo the steps above for the other conditions as well

The formulas would exist:

=AND(C2>=half dozen,C2<=xiv)

=AND(C2>=15,C2<=29)

=C2>=30

Hope this helps.

Regards,

Ashish Mathur
world wide web.ashishmathur.com
http://twitter.com/excelashish

15 people institute this reply helpful

Was this answer helpful?

Sad this didn't help.

Great! Thank you for your feedback.

How satisfied are you lot with this respond?

Thanks for your feedback, information technology helps united states improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Source: https://answers.microsoft.com/en-us/msoffice/forum/all/how-do-you-change-cell-color-automatically-based/d883b136-d1d9-4dbf-b642-7e51041a12d8

Posted by: morganmoseng.blogspot.com