Forum Discussion
Changing a cell Colour based on amount of time since lasted updated
Let's say the cell with the dropdown is D2 (or if there are multiple cells, D2:D50 for example).
We'll use column E to record the time the cell in column D was last changed.
Right-click the sheet tab.
Select 'View Code' from the context menu.
Copy the following code into the worksheet module:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Set rng = Range("D2") ' or Range("D2:D50")
If Not Intersect(rng, Target) Is Nothing Then
Application.ScreenUpdating = False
Application.EnableEvents = False
Intersect(rng, Target).Offset(0, 1).Value = Now
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub
Switch back to Excel.
Select D2 (or D2:D50).
On the Home tab of the ribbon, select Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula
=AND(D2<>"",D2<>"Invoiced",D2<>"Cancelled",E2<NOW()-45)
Click Format...
Activate the Fill tab.
Select amber.
Click OK, then click OK again.
Repeat the above steps, but with the formula
=AND(D2<>"",D2<>"Invoiced",D2<>"Cancelled",E2<NOW()-90)
and red as fill color.
Finally, save the workbook as a macro-enabled workbook (*.xlsm), and make sure that you allow macros when you open it.