Need help modifying Macro to compare 2 workbooks instead of 2 sheets

[adibakale]

The following macro compares Column A in Sheet1 and Column A in Sheet2. If the values match, it highlights the cell red.

I need help modifying the code so that this compares Workbook1 and Workbook2 instead of Sheet1 and Sheet2.

I currently have to copy and paste from 1 workbook to the other to run this macro, I would like to compare 2 different workbooks instead.

Macro finds duplicates in ColumnA of Sheet1 & Sheet2
Need macro to compare duplicates of Workbook1 & Workbook2

Any help with this would be greatly appreciated

Sub find_duplicates()
'LR1 should be Workbook1, Sheet1, Column A
'LR2 should be Workbook2, Sheet1, Column A
LR1 = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row 'last row in old
LR2 = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row 'last row in new
For i = 2 To LR1 'loop thru all the cells in Col A of old
For j = 2 To LR2 'loop thru all the cells in Col A of new
'if the cells match then color them red
If Sheets("Sheet1").Cells(i, "A") = Sheets("Sheet2").Cells(j, "A") Then
Sheets("Sheet1").Cells(i, "A").Interior.Color = RGB(255, 0, 0) 'Red
Sheets("Sheet2").Cells(j, "A").Interior.Color = RGB(255, 0, 0) 'Red
End If
Next j
Next i
End Sub

 

[JLGWhiz]

The code below assumes sheet 1 of each workbook.

Sub hiLiteDups()
Dim wb1 As Workbook, wb2 As Workbook, sh1 As Worksheet, sh2 As Worksheet, lr As Long, c As Range, fn As Range, fAdr As String
Set wb1 = Workbooks(1) 'This will be the host for the macro and should be opened first
Set wb2 = Workbooks(2) 'This will be the target workbook and should be opened second.
'as an alternative to opening in a certain sequence, you could use the workbook name instead of the index numbers.
Set sh1 = wb1.Sheets(1) 'Edit sheet name
Set sh2 = wb2.Sheets(1) 'Edit sheet name
lr = sh1.Cells(Rows.Count, 1).End(xlUp).Row
    For Each c In sh1.Range("A2:A" & lr)
        Set fn = sh2.Range("A:A").Find(c.Value, , xlValues, xlWhole)
            If Not fn Is Nothing Then
                fAdr = fn.Address
                Do
                    c.Interior.ColorIndex = 3
                    fn.Interior.ColorIndex = 3
                    Set fn = sh2.Range("A:A").FindNext(fn)
                Loop While fn.Address <> fAdr
            End If
    Next
End Sub

 

[adibakale]

Thanks so much, I really appreciate it.

sorry for not requesting this earlier, but I didn't realize it until now.

Is it possible to:

Open the first excel file
Run the Macro
Use something like fdialog to open and select the second file to compare?

I am still working on it myself and haven't been able to do it yet (still learning).

Thanks

 

[JLGWhiz]

Thanks so much, I really appreciate it.

sorry for not requesting this earlier, but I didn't realize it until now.

Is it possible to:

Open the first excel file
Run the Macro
Use something like fdialog to open and select the second file to compare?

I am still working on it myself and haven't been able to do it yet (still learning).

Thanks

Sub hiLiteDups()
Dim wb1 As Workbook, wb2 As Workbook, sh1 As Worksheet, sh2 As Worksheet, lr As Long, c As Range, fn As Range, fAdr As String
Set wb1 = Workbooks(1) 'This will be the host for the macro and should be opened first
fName = Application.GetOpenFilename("Excel Files (*.xl*), *.xl*")
Set wb2 = Workbooks.Open(fName) 'Assumes workbooks are same directory, if not need to add directory as part of file name (fPath & "\" & fName)
'as an alternative to opening in a certain sequence, you could use the workbook name instead of the index numbers.
Set sh1 = wb1.Sheets(1) 'Edit sheet name
Set sh2 = wb2.Sheets(1) 'Edit sheet name
lr = sh1.Cells(Rows.Count, 1).End(xlUp).Row
    For Each c In sh1.Range("A2:A" & lr)
        Set fn = sh2.Range("A:A").Find(c.Value, , xlValues, xlWhole)
            If Not fn Is Nothing Then
                fAdr = fn.Address
                Do
                    c.Interior.ColorIndex = 3
                    fn.Interior.ColorIndex = 3
                    Set fn = sh2.Range("A:A").FindNext(fn)
                Loop While fn.Address <> fAdr
            End If
    Next
End Sub