Comparing data in multiple columns to another sheet Closed

Question

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


Configuration: Windows 7 / Mozilla 11.0

TrowaD · Answer

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.

Comparing data in multiple columns to another sheet

1 response

Similar discussions

Subscribe To Our Newsletter!