How to highlight data in Excel using worksheet change event

A user wants to highlight data automatically in specific cells in an Excel worksheet based on a date that he enters in a specific cell. We can achieve this goal by using the worksheet change event.

Watch the training video:

Watch this video on YouTube.

Here’s the complete VBA code for the worksheet change event to highlight specific data automatically:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long
Dim lastrow As Long
lastrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
If Target.Column = 3 Then
For i = 2 To lastrow
If IsDate(Cells(i, “C”)) = False Then
Range(“A” & i & “:” & “C” & i).Interior.ColorIndex = xlNone
Range(“A” & i & “:” & “C” & i).Font.ColorIndex = 1
Else
‘red 3, blue 37
Range(“A” & i & “:” & “C” & i).Interior.ColorIndex = 3
Range(“A” & i & “:” & “C” & i).Font.ColorIndex = 2
End If
Next
End If
‘http://dmcritchie.mvps.org/excel/colors.htm – for all color indices
End Sub

How to highlight data in Excel using worksheet change event.
How to highlight data in Excel using worksheet change event.