Compare Rows via VBA Loop

[wildcatcrazy]

I have a simple macro that I recorded and then edited in VB. But, I would like to turn this into a loop so I do not have to make over a 1000 entries just to compare the remaining rows. What I need it to do is compare rows 2:3, 4:5, 6:7, so and so on to highlight the differences in the columns until it reaches the end of the data. When it does the compare, it needs to highlight the changes and move on to the next set of rows. I was able to duplicate these entries to compare all of the remaining rows (two at a time) until I got to around 500. Then Excel said I exceeded the limit.

Here is the macro I started with:

Sub Compare()
'
' Compare Macro
'
' Keyboard Shortcut: Ctrl+l
'

Rows("2:3").Select
Selection.ColumnDifferences(ActiveCell).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End Sub

 

[brettdj]

Something like this will compare in pairs from row 2 to the last row

The code
- uses error handling in case there are no differences between the paired rows
- collates any differences into a range (rng3)
- formats rng3 at the end if it exists

If you are doing lots of spreadsheet auditing you may find my Mappit! addin useful, see http://www.experts-exchange.com/A_2613.html

Sub PairedRows()
    Dim rng1 As Range
    Dim rng2 As Range
    Dim rng3 As Range

    Dim lngRow As Long
    Set rng1 = ActiveSheet.Cells.Find("*", [a1], xlValues, , xlByRows, xlPrevious)
    On Error Resume Next
    For lngRow = 2 To rng1.Row Step 2
        Set rng2 = Nothing
        Set rng2 = Rows(lngRow & ":" & lngRow + 1).ColumnDifferences(Rows(lngRow).Cells(1))
        If Not rng2 Is Nothing Then
            If Not rng3 Is Nothing Then
                Set rng3 = Union(rng2, rng3)
            Else
                Set rng3 = rng2
            End If
        End If
    Next
    On Error GoTo 0
    If Not rng3 Is Nothing Then rng3.Interior.Color = 65535
End Sub

 

[wildcatcrazy]

Brett, you are awesome! Just what I needed . Thanks!

 

[brettdj]

Glad to have helped, thx for the feedback