Uploaded August 2019 | Updated September 2026, 2 weeks ago

---

How to highlight differences in 2 lists automatically using VBA.
Here's the complete VBA code:

Option Explicit

Sub highlightdifferencesFinal()

Application.ScreenUpdating = False

Dim i As Long, j As Long, lastrowsht1 As Long, lastrowsht2 As Long, lastcolumn As Long, k As Long

Sheet1.Activate

lastrowsht1 = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To lastrowsht1

Sheet2.Activate

lastrowsht2 = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
lastcolumn = Sheet2.Cells(1, Columns.Count).End(xlToLeft).Column

For j = 2 To lastrowsht2

For k = 1 To lastcolumn

If Sheet2.Cells(j, k) = Sheet1.Cells(i, k) Then

Sheet2.Cells(j, k).Interior.ColorIndex = 32 ' blue color
Sheet2.Cells(j, k).Font.Color = vbWhite

End If

Next k

Next j

Sheet1.Activate

Next i

Application.ScreenUpdating = True

End Sub

Option Explicit

Sub clearFormatsSht2()
Dim i As Long, lastrowsht2 As Long

lastrowsht2 = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row

Sheet2.Range("A2:C" & lastrowsht2).clearFormats


End Sub

If you liked the video share it with your friends on Social media too.
More details available on our website:
exceltrainingvideos.com/highlight-differences-in-2-lists-automatically