Forum Discussion
colour changing cells
Good afternoon
I have a spreadsheet which is used to manually input data including a date a customer is coming in to pick up an order. (That date is in cell B3)
The Cell in G3 is going to be the cell that i manually update when the order has come into the building and is ready for collection.
Is it possible for the cell to change colour the closer it gets to the collection date? i.e. it starts yellow and as it gets closer to the date of collection it gets more and more red? Once i input a date in the cell to say it's arrived, can it then turn green?
Is that doable?
Thank you in advance
Lx
Excel does not provide color scale conditional formatting for a single cell.
You can create a set of rules, for example:
Select G3.
Set its fill color yo yellow. This will be the default.
On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula=B3<=TODAY()+10
Click Format...
Activate the Fill tab.
Select orange as highlight color.
Click OK, then click OK again.Repeat these steps but with the formula
=B3<=TODAY()+5
and red as fill color.
Repeat them again with the formula
=B3<=TODAY()+1
and dark red.
Finally, repeat them with the formula
=G3<>""
and green.
- That makes sense thank you!
It's definitely possible. What I've attached does it. I deviated from your description just a bit, so that rather than adding a date when the item comes in, you simply select a word "arrived" from a drop down. That word turns the cell green. In the absence of that, whether the word is "waiting" or G3 is simply blank, there's a "helper cell" that tracks the difference between today's date and the date scheduled for the customer to pick up. That helper cell can be off to the side, and even in a hidden column. The shorter the period, and you can adjust these in the Conditional Formatting dialog box, the redder it gets.
Note: there may be more streamlined ways to do this in conditional formatting, and I'd love to see what others come up with.
- Thank you!
