Comparing data in multiple columns to another sheet

Closed
savagea Posts 1 Registration date Tuesday April 26, 2016 Status Member Last seen April 27, 2016 - Apr 27, 2016 at 08:29 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - May 3, 2016 at 12:03 PM
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 response

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
May 3, 2016 at 12:03 PM
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.
0