Compare 2 sheets of a column and highlight unmatched excel vba

[arnelgp]

you may try this, paste this on a Module:

Public Sub mySub()
Dim i As Integer
Dim rng As Range
With Sheet1
    For i = 1 To .Range("a1").SpecialCells(xlCellTypeLastCell).Row
        Set rng = .Range("e" & i)
        If WorksheetFunction.CountIf(Sheet2.Range("a:a"), rng) = 0 Then
            rng.Interior.Color = vbRed
        End If
    Next
End With
End Sub

on Sheet1, add this code:

Private Sub Worksheet_Change(ByVal Target As Range)
Call mySub
End Sub