Trying to highlight unique values across multiple sheets in Excel 2007

Question

I am trying to compare the values from column A of Sheet1 (which I have renamed "AR") and column A of Sheet2 (which I have renamed "Paste Here") and highlight rows containing values that do not exist on the other sheet. (Both columns end with an empty cell.) I found a macro that I thought did something similar to what I wanted here at Efficient removal of duplicate records across multiple Excel sheets, and I am trying to modify it to meet my needs, but I can't make it work. Currently when I run it, it locks Excel up and I have to do end task on Excel.

This is what I have so far:

Option Explicit

Sub Compare2()
    Application.ScreenUpdating = False

    Dim startRow As Integer
    startRow = 1

    Dim row As Integer
    row = startRow

    Dim bRow As Integer

    'sharks below, cap'ain
    ' This loop is looping on row.
    ' Scan down column AR!A (i.e., Sheet1!A) until we find an empty cell.
    Do While (Worksheets("AR").Range("A" & row).Value <> "")

        Dim aVal As String
        aVal = Worksheets("AR").Range("A" & row).Value
        bRow = startRow             'I see thy booty

        ' This loop is looping on bRow.  Scan down column 'Paste Here'!A
        ' (i.e., Sheet2!A) until we find an empty cell.
        Do While (Worksheets("Paste Here").Range("A" & bRow).Value <> "")

            Dim aVal2 As String
            aVal2 = Worksheets("Paste Here").Range("A" & bRow).Value

            If (aVal <> aVal2) Then
                Worksheets("AR").Rows(row).Interior.ColorIndex = 6
                                ' we found a traitor; feed 'em to the sharks
                row = row - row
                Exit Do
            End If

            If (aVal2 <> aVal) Then
                Worksheets("Paste Here").Rows(row).Interior.ColorIndex = 6
                row = row - row
                Exit Do
            End If

            bRow = bRow + 1
        Loop

        row = row + 1
    Loop

End Sub

Any help you can provide would be helpful.

score 0 · Answer 1 · answered Mar 22 '15 at 20:49

It’s too bad that you didn’t understand the subroutine that you were cannibalizing better before you started modifying it.

The “WalkThePlank” subroutine is looking for matches. When it finds a row in Sheet1 that matches a row in Sheet2, it takes action. You’re looking for unique values; i.e., rows in Sheet1 that don’t match anything in Sheet2, and vice versa. But what you’re doing is triggering off pairs of rows that don’t match; i.e., if Sheet1!Row 1 doesn’t match Sheet2!Row 1, you take action. This is premature; you need to scan all the way through Sheet2 to determine whether Sheet1!Row 1 matches any row in Sheet2.
The author of the “WalkThePlank” subroutine did something that doesn’t make a lot of sense. After deleting the row indexed by the row variable, he set the variable to 0. This was wasteful, as it caused the subroutine to re-examine all the Sheet1 rows that it had already examined and determined to be unique. But, since it deletes the row that’s not unique (i.e., that matches row bRow on Sheet2), it won’t look at that row again, and there isn’t an infinite loop. To compound the curiosity, he could have said row = 0, but, instead, he cryptically said row = row - row.

But you need to change that. (To be more accurate, I believe that you should just eliminate the row = … statements inside the Do While loops; except for the row = row + 1 at the end.) Since you aren’t deleting rows, this does cause your macro to re-examine the same data over and over again; i.e., it is an infinite loop. You need to just keep on marching down Sheet1.
You may be able to abort a VBA infinite loop without killing Excel and losing your data by typing Ctrl+Break. If your keyboard doesn’t have a Break key, try bringing up the on-screen keyboard by running osk and clicking on Ctrl and then Pause. If that doesn’t work, try Ctrl and then ScrLk.

Super User is not a script-writing service. I think I’ve explained what’s going wrong well enough that you should be able to fix it now. If you still can’t get it to work, come back and tell us where you got stuck.

Trying to highlight unique values across multiple sheets in Excel 2007

1 Answers1