Forum Discussion

Torren1's avatar
Torren1
Copper Contributor
Nov 07, 2021

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

Hi

 

does anyone know if it is possible to change a cells colour based on how long it has been since it was lasted change (the cell contains a drop down list)

 

E.g 

 

if the status in the drop down menu hasn't been change in 45 days change it to amber then after 90 days change it to Red. also every time the status has been change to reset the time then when it reaches its final status "Invoiced" or "Cancelled" to Ignore the formula or formatting? 

Formulas and Functions

1 Reply

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Nov 07, 2021

    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.

Resources