» Change font color based on Date using VBA in Microsoft Excel

Question:
How can I change the font color of a Cell to Red if the date is greater than today?


Answer:
Enter the following Code with Excel 5 or 7; for Excel 97 and above, place the code in the This Workbook module.Then assign it to a button to run the macro.

Private Sub Workbook_Open()

   With Worksheets("Data")

      .OnEntry = "SetColor"

      .OnSheetActivate = "ResetColor"

   End With

End Sub



Private Sub Workbook_BeforeClose(Cancel As Boolean)

    With Worksheets("Data")

      .OnEntry = ""

      .OnSheetActivate = ""

   End With

End Sub



'Place the code below into the standard  module



Sub SetColor()

    If ActiveCell.Value > Date Then

       ActiveCell.Interior.ColorIndex = 3

    Else

       ActiveCell.Interior.ColorIndex = xlNone

    End If

End Sub



Sub ResetColor()

   Dim rng As Range

   Dim intRow As Integer, intCol As Integer

   For Each rng In Range("A1:B5").Cells

      If Not IsEmpty(rng) Then

         If DateValue(rng.Value) > Date Then

            rng.Interior.ColorIndex = 3

         Else

            rng.Interior.ColorIndex = xlNone

         End If

      End If

   Next rng

End Sub