How to use VBA code to compare two different sheets in a workbook
Category: [Excel] Demo Available
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.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to copy columns from multiple workbooks and paste into one worksheet |
| How to copy range from variable named workbook to current workbook |
| How to use VBA to send email (outlook) with title of workbook |
| How to import data in a specific sheet from another workbook (sheet number must be variable) |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.