Excel Help Forum - View Single Post - [SOLVED] Compare two files with the same ID

JBeaucaire · #6 07-10-2009, 02:03 PM

Run this macro from inside the Master file. It will open the UPDATE file from your default directory, update the values and close the update file. If you don't want the update file closed, take out that line of code near the bottom.

Code:

Option Explicit
Sub UpdateMasterFile()
'JBeaucaire  (7/10/2009)
Dim LR As Long, Rw As Long, i As Long
Application.DisplayAlerts = False
Application.ScreenUpdating = False

Workbooks.Open ("Update.xls")
LR = Range("A" & Rows.Count).End(xlUp).Row

    For i = 2 To LR
        Rw = 0
        On Error Resume Next
        Rw = WorksheetFunction.Match(Range("A" & i), Workbooks("Master.xls").Sheets("Sheet1").Range("A:A"), 0)
        
        If Rw > 0 Then
            With Workbooks("Master.xls").Sheets("Sheet1").Cells(Rw, "B")
                If Not .Value = Cells(i, "B") Then
                    .Value = Cells(i, "B")
                    .Interior.ColorIndex = 6
                End If
            End With
            With Workbooks("Master.xls").Sheets("Sheet1").Cells(Rw, "C")
                If Not .Value = Cells(i, "C") Then
                    .Value = Cells(i, "C")
                    .Interior.ColorIndex = 6
                End If
            End With
            With Workbooks("Master.xls").Sheets("Sheet1").Cells(Rw, "D")
                If Not .Value = Cells(i, "D") Then
                    .Value = Cells(i, "D")
                    .Interior.ColorIndex = 6
                End If
            End With
        
        End If
    Next i

Workbooks("Update.xls").Close False
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub