compare two rows on the same worksheet

Question

I am trying to do a "for each" sub in VBA, comparing two pairs of rows and the values in each cell to one another. For example row 2 is compared with row 3, row 4 is compared with row 5 etc. I need the code to highlight the differences in each cell for each of the comparisons. This is what I have so far and I cannot seem to get it to work. Any thought?

Sub testing_2()

Dim rw_2 As Range, rw_1 As Range, decisions As String

decisions = MsgBox("Check accuracy?", vbYesNo)

If decisions = vbYes Then

    For Each rw_1 In Worksheets("worksheet").Rows
        For Each rw_2 In Worksheets("worksheet").Rows
            If Not StrComp(rw_1.row Mod 2 = 0, rw_2.row Mod 2 = 1, vbBinaryCompare) = 0 Then
                Range(rw_1.row Mod 2 = 0, rw_2.row Mod 2 = 1).Interior.ColorIndex = 6
            End If
        Next rw_2
    Next rw_1

Else: End If

End Sub

Thank you!

Basically, I am looking at each row, two at a time, and highlighting the different values between them.

This can be done with conditional formatting by the way... and don't loop through every single row of the worksheet, but [find the last one](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) first. — BigBen, May 21 '20 at 18:53
also, `StrComp` cannot compare a full row to another full row. Are there key columns that denote a duplicate? — Scott Craner, May 21 '20 at 19:00
There is not enough information here to help you. please mock up some data that demonstrates what you want to do. — Scott Craner, May 21 '20 at 19:08
@ScottCraner I edited the post with an image if it helps. Sorry if my wording is confusing — Jordan Dekker, May 21 '20 at 19:26

score 0 · Answer 1 · edited May 23 '20 at 17:18

One loop to to loop the rows stepping 2 rows at a time and another loop to loop the columns

Sub testing_2()

    decisions = MsgBox("Check accuracy?", vbYesNo)

    If decisions = vbYes Then

        With Worksheets("Sheet4") ' change to your sheet
             Dim lstRw As Long
             lstRw = .Cells(.Rows.Count, 1).End(xlUp).Row

             Dim lstClm As Long
             lstClm = .Cells(1, Columns.Count).End(xlToLeft).Column

             Dim i As Long
             For i = 2 To lstRw Step 2
                Dim j As Long
                For j = 2 To lstClm
                    If .Cells(i, j) <> .Cells(i + 1, j) Then
                        .Range(.Cells(i, j), .Cells(i + 1, j)).Interior.ColorIndex = 6
                    End If
                Next j
            Next i

        End With
    End If

End Sub

compare two rows on the same worksheet

1 Answers1