Excel, comparing two specific rows in two sheets and highlighting the differences

Question

Before commenting on saying that there are similar questions, Ive tried them but they do not work unfortunately

Hi, this is the first time I am on S.O, rest assured, I have spent hours looking for a solution for this. I have a status column which shows statuses such as, deleted, new, changed. When the status is "changed", I would like to compare that particular row from column E to the last possible column in Excel (XFD) in Sheet3 to columns A to the last possible column in Excel (XFD) in Sheet1 and highlight the cells which are different.

I have found this solution:-

Dim diffB As Boolean
  Dim r As Long, c As Integer, m As Integer
  Dim lr1 As Long, lr2 As Long, lc1 As Integer, lc2 As Integer
  Dim maxR As Long, maxC As Integer, cf1 As String, cf2 As String
  Dim rptWB As Workbook, DiffCount As Long
  Application.ScreenUpdating = False
  Application.StatusBar = "Creating the report..."
  Application.DisplayAlerts = True
  With Sheet1.UsedRange
    lr1 = .Rows.Count
    lc1 = .Columns.Count
  End With
  With Sheet3.UsedRange
    lr2 = .Rows.Count
    lc2 = .Columns.Count
  End With
  maxR = lr1
  maxC = lc1
  If maxR < lr2 Then maxR = lr2
  If maxC < lc2 Then maxC = lc2
  DiffCount = 0
  For c = 1 To maxC
    For i = 2 To lr1
      diffB = True
      Application.StatusBar = "Comparing cells " & Format(i / maxR, "0 %") & "..."
        For r = 2 To lr2
          cf1 = ""
          cf2 = ""
          On Error Resume Next
          cf1 = Sheet1.Cells(i, c).FormulaLocal
          cf2 = Sheet3.Cells(r, c).FormulaLocal
          On Error GoTo 0
          If cf1 = cf2 Then
            diffB = False
            Sheet1.Cells(i, c).Interior.ColorIndex = 19
            Sheet1.Cells(i, c).Select
            Selection.Font.Bold = True
            Exit For
          End If
        Next r

     If diffB Then
       DiffCount = DiffCount + 1
       Sheet1.Cells(i, c).Interior.ColorIndex = 0
       Sheet1.Cells(i, c).Select
       Selection.Font.Bold = False
     End If
    Next i
  Next c3
Application.StatusBar = "Formatting the report..."
'Columns("A:IV").ColumnWidth = 10
m = maxR - DiffCount - 1
Application.StatusBar = False
Application.ScreenUpdating = True
MsgBox m & " cells contain same values!", vbInformation, _
"Compare " & Sheet1.Name & " with " & Sheet3.Name

However, this compares columns and I do not know how to limit the comparison to column E-XFD in sheet1 to column A-XFD in sheet2.

There are also several sheets in this workbook but I only want to compare sheet1 and sheet2.

It will be much appreciated if you guys can help me out :)

Thanks!

Welcome to SO! It is helpful to link to posts you've looked at and tell us why you find them unsatisfactory - that helps us refine answers. I'm confused, you want to compare X columns in Sheet 2 to X-5 columns in Sheet 1? What's your algorithm for doing so? Are you comparing multiple columns in Sheet1 against the same column in Sheet2? Are there some columns in Sheet2 that you'll be skipping that you forgot to mention? — FreeMan
Instead of For c = 1 To maxC you'll need to define the first column (not 1). — user3819867
@FreeMan, I want to compare all columns in Sheet 1 and Sheet 2(without the first 4 columns) as they have similar headings. I am not sure what algorithm I should use. The columns in Sheet 2 that I am skipping are the first 4 columns. — Nash
@user3819867 if I define the first column to 4, will that affect both sheets? — Nash

Moritz Schmitz v. Hülst Moritz Schmitz v. Hülst · Accepted Answer · 2015-04-17T14:23:09

Dim lrOne As Integer
Dim lcOne As Integer
Dim lrTwo As Integer
Dim lcTwo As Integer
Dim cellA As Variant
Dim cellB As Variant
Dim cellCnt As Integer
Dim lookupRange As Range
Dim lookinRange As Range

lrOne = Sheet1.Cells(Rows.Count, 5).End(xlUp).Row
lrTwo = Sheet3.Cells(Rows.Count, 1).End(xlUp).Row
lcOne = Sheet1.Cells(1, Columns.Count).End(xlToLeft).Column
lcTwo = Sheet3.Cells(1, Columns.Count).End(xlToLeft).Column

Set lookupRange = Sheet1.Range(Cells(1,5), Cells(lrOne, lcOne))
Set lookinRange = Sheet3.Range(Cells(1,1), Cells(lrTwo, lcTwo))

For Each cellA In lookupRange
    For Each cellB in lookinRange
        If cellA.Value = cellB.Value And cellA.Value <> "" Then
            cellB.Interior.ColorIndex = 3
            cellCnt = cellCnt + 1
        End If
    Next cellB
Next cellA

Excel, comparing two specific rows in two sheets and highlighting the differences

1 Answers