Conditional Formatting of Dates (Multiple Criteria)

[DocAElstein]

Hello msiyab,
Possibly Solution 1: This could be to refresh the range of dates in column A so that initially all the yellow highlighting is cleared.
( We do not want to risk changing anything you have in cells A1 and A2, so we can use the Offset( , ) property on the range followed by the Resize( , ) Property to adjust the range dimensions. )

Sub ChkChqe()
Dim Ws1 As Worksheet
 Set Ws1 = ThisWorkbook.Worksheets.Item(1)
Dim rngA As Range: Set rngA = Ws1.Range("A1:A" & Ws1.UsedRange.Rows.Count & "")
 Let rngA.Offset(2, 0).Resize(Ws1.UsedRange.Rows.Count - 2, 1).Interior.TintAndShade = 0
Dim rngL As Range: Set rngL = Ws1.Range("L1:L" & Ws1.UsedRange.Rows.Count & "")
Dim arrA() As Variant, arrL() As Variant
 Let arrA() = rngA.Value2: Let arrL() = rngL.Value2
Dim Nah As Long: Let Nah = Now
Dim Cnt As Long
    For Cnt = 3 To Ws1.UsedRange.Rows.Count
        If arrA(Cnt, 1) = "" Then
        ' Do nothing
        Else
          If arrL(Cnt, 1) = "" And (Nah - arrA(Cnt, 1)) >= 150 Then rngA.Item(Cnt).Interior.Color = vbYellow
        End If
    Next Cnt
End Sub

Possibly Solution 2: This would be an advancement and modification to Solution 1: Procedure Sub ChkChqe() from above, could be converted into an “Events type” procedure . This would then run the routine automatically when you changed any value. This would simply save you having to run the procedure yourself.

Alan

[msiyab]

Hello msiyab,
Possibly Solution 1: This could be to refresh the range of dates in column A so that initially all the yellow highlighting is cleared.
( We do not want to risk changing anything you have in cells A1 and A2, so we can use the Offset( , ) property on the range followed by the Resize( , ) Property to adjust the range dimensions. )

This solution seems to be working. But I need to run the macro manually to update the conditional formatting every time.

Possibly Solution 2: This would be an advancement and modification to Solution 1: Procedure Sub ChkChqe() from above, could be converted into an “Events type” procedure . This would then run the routine automatically when you changed any value. This would simply save you having to run the procedure yourself.

How can we make this file a "Events type" macro so that the macro runs automatically?