Comparison of records between two spreadsheets

[Carolyn at VW]

We have two spreadsheets that are identical, one went through a filtering
process in an attempt to automate the reporting of the records. After doing
so we are noticed there were a number of records missing. Instead of
manually going through the sheets, we would like to set something up to
automatically tell us which records/rows are missing from the other sheet.

 

[Otto Moehrbach]

Carolyn
You can use the following macro. Make sure the first sheet, the sheet
that has all the data, is the active sheet. I assumed in this macro that
the sheet with the missing data is named "Two". Change that as you wish.
This macro loops through all the entries in Column A (starting with A2) of
the first sheet and checks if that entry is in Column A of the second sheet.
If it is NOT, the macro will color that cell in the first sheet RED. Change
the color as you wish also. HTH Otto
Sub FindMissingRecords()
Dim ColAOne As Range
Dim ColATwo As Range
Dim i As Range
Set ColAOne = Range("A2", Range("A" & Rows.Count).End(xlUp))
With Sheets("Two")
Set ColATwo = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
End With
For Each i In ColAOne
If ColATwo.Find(What:=i.Value, LookAt:=xlWhole) Is Nothing Then
i.Interior.ColorIndex = 3
Next i
End Sub

 

[Carolyn at VW]

Thanks for your quick response, I created the macro as you stated, I copied
the second file into another worksheet and named it "Two" as you indicated
but I'm receiving a compiling error and was wondering if I could ask for your
help again. The error is "Next without for" When I hit the help key this is
the response:

"A Next statement must have a preceding For statement that matches. This
error has the following cause and solution:

A Next statement is used without a corresponding For statement.
Check other control structures within the For...Next structure and verify
that they are correctly matched. For example, an If without a matching End If
inside the For...Next structure generates this error.

 

[Kevin Vaughn]

I am replying to this one even though I intended to reply to the last message
(but this one has the code I wanted to refer to.)

I believe I can point you in the right direction based on the error you were
getting and the code offerred.

Most likely you are getting an error because of unintended line wrap. The
If statement should either be entirely on one line (which means that
i.Interior.ColorIndex = 3 should be on the same line as the If statement, or
you need to use an End If statement on the line following
i.Interior.ColorIndex = 3.

Either should work but because of line wrap it appears that that line is on
a line all by itself (and there is no End if) This is more than likely why
you are getting the error you are seeing.

HTH

 

[Otto Moehrbach]

Carolyn
Either do what Kevin suggests or have "Then" at the end of the line and
add a space and an underscore character after the Then, as in "Then _".
Don't leave out the space. Otto

 

[Kevin Vaughn]

Right. I forgot that one, didn't I?