Hello
please could someone tell me a vba code that compares 2 sheets in the same workbook and highlights the difference.
The two sheets have text in it.
Thanks in advance
Hello
please could someone tell me a vba code that compares 2 sheets in the same workbook and highlights the difference.
The two sheets have text in it.
Thanks in advance
Do you want to compare all the cells in the entire sheet or just one particular row or column?
I want to compare range A1: N2781 in both the sheets.
Try this macro. It will highlight the cells in each sheet that are not in the other sheet.
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
Display More
Don’t have an account yet? Register yourself now and be a part of our community!