Experts Exchange connects you with the people and services you need so you can get back to work.
Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
Dim rng1 As Range, rng2 As Range, rng3 As Range
Dim X1(), X2(), X3()
Dim i As Long, j As Long
Set ws1 = Application.InputBox("Click in any cell in the first sheet", "Select sheet1", , , , , , 8).Parent
Set ws2 = Application.InputBox("Click in any cell in the second sheet", "Select sheet2", , , , , , 8).Parent
If ws1.Name = ws2.Name Then
MsgBox "You picked the same 2 sheets", vbCritical
.ScreenUpdating = False
.DisplayAlerts = False
Set rng1 = ws1.UsedRange
Set rng2 = ws2.Range(rng1.Address)
On Error Resume Next
On Error GoTo 0
Set ws3 = Worksheets.Add
ws3.Name = "Output"
Set rng3 = ws3.Range(rng1.Address)
X1 = rng1
X2 = rng2
X3 = rng3
For i = 1 To UBound(X1, 1)
For j = 1 To UBound(X1, 2)
If X1(i, j) <> X2(i, j) Then
X3(i, j) = "'" & X1(i, j) & " v " & "'" & X2(i, j)
rng1.Cells(i, j).Interior.ColorIndex = 4
rng3 = X3
.ScreenUpdating = True
.DisplayAlerts = True
Open in new window
Your question, your audience. Choose who sees your identity—and your question—with question security.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Please enter a first name
Please enter a last name
Must be at least 4 characters long.
Join and Comment
From novice to tech pro — start learning today.
Premium members can enroll in this course at no extra cost.
Premium members get this course for $159.20.
Premium members get this course for $95.20.
Premium members get this course for $389.00.
Premium members get this course for $122.40.
Premium members get this course for $299.99.