Changing a cell Colour based on amount of time since lasted updated

Torren1

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.