Comparing data in multiple columns to another sheet [Closed]

Report
Posts
2
Registration date
Tuesday April 26, 2016
Status
Member
Last seen
April 27, 2016
-
Posts
2757
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 8, 2021
-
Hello,
In Excel 2010, I have a sheet of names and addresses that I would like to compare to a new report of names and addresses to find any names that have changed and also to find any addresses that weren't previously listed. In the resulting sheet, I would like to highlight any different names along with the new addresses.

For example, in the first sheet there might be Mary Smith (in A1) living at 3222 Main Street (B1). If in the second sheet Joe Jones (in A1 or possibly another cell if there are other addresses that got added above him) now lives at 3222 Main Street (again, it could possibly be in a different cell than it was in the first sheet), I would like to highlight that difference. Hopefully I have explained this clearly?

If anyone has suggestions or thoughts, I would be very grateful to hear them! :)

Thank you in advance for your time.
Angela in Indiana


1 reply

Posts
2757
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 8, 2021
462
Hi Angela,

In the code below the sheet of names and addresses is called Sheet1 and the new report sheet is called Sheet2.

The code will loop through column B of Sheet2. When the address is not found (new address) or the corresponding name doesn't match (new occupant) on Sheet1, the name and address on Sheet2 will turn orange.

Hopefully it is how you imagined it.

Here is the code:
Sub RunMe()
Dim x As Integer, vFound As Range

Sheets("Sheet2").Select

For Each cell In Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row)
    Set vFound = Sheets("Sheet1").Columns("B:B").Find(what:=cell)
    If vFound Is Nothing Then
        Range(Range("A" & cell.Row), Range("B" & cell.Row)).Interior.Color = 49407
    ElseIf cell & cell.Offset(0, -1) <> vFound & vFound.Offset(0, -1) Then
        Range(Range("A" & cell.Row), Range("B" & cell.Row)).Interior.Color = 49407
    End If
Next cell
End Sub


Best regards,
Trowa
Monday, Tuesday and Thursday are usually the days I'll respond. Bear this in mind when awaiting a reply.

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!