How to use Vba code to compare two different sheets in a workbook

How to use VBA code to compare two different sheets in a workbook

Requirement:

The user requires a VBA code that compares 2 sheets in the same workbook and highlights the difference.

The two sheets have text in it.

Solution:

This macro will highlight the cells in each sheet that are not in the other sheet.

Code:

Sub CompareSheets()
    Application.ScreenUpdating = False
    Dim rngSH1 As Range, rngSH2 As Range
    Set rngSH1 = Sheets("Sheet1").Range("A1: N2781")
    Set rngSH2 = Sheets("Sheet2").Range("A1: N2781")
    Dim Rng As Range, RngList As Object
    Set RngList = CreateObject("Scripting.Dictionary")
    For Each Rng In rngSH2
      If Not RngList.Exists(Rng.Value) Then
        RngList.Add Rng.Value, Nothing
      End If
    Next
    For Each Rng In rngSH1
      If Not RngList.Exists(Rng.Value) Then
        Rng.Interior.ColorIndex = 6
      End If
    Next
    RngList.RemoveAll
    For Each Rng In rngSH1
      If Not RngList.Exists(Rng.Value) Then
        RngList.Add Rng.Value, Nothing
      End If
    Next
    For Each Rng In rngSH2
      If Not RngList.Exists(Rng.Value) Then
        Rng.Interior.ColorIndex = 3
      End If
    Next
    Application.ScreenUpdating = True
End Sub

Obtained from the OzGrid Help Forum.

Solution provided by Mumps.