Need help modifying a Macro that highlights duplicates in 2 worksheets

[adibakale]

This Macro compares Worksheet1 Column A and Worksheet2 Column A and highlights and duplicates.

I would like to be able to do the same thing, but with 2 different workbooks.

I need to be able to open Workbook1, use fdialog to choose Workbook2, then use this macro to compare Workbook1.Sheet1.ColumnA and Workbook2.Sheet1.ColumnA

I am fairly new to VBA and have tried to do this but haven't been able to get it working correctly. Any help with this would be greatly appreciated.

Here is the Macro that I have now:

---

Sub find_duplicates()
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

 

[Worf]

Please test this:

Sub OneLoop()
Dim i%, j, wb As Workbook, fn, ws As Worksheet, n, fc$
fn = Application.GetOpenFilename("Excel files, *.xls, All Files, *.*", 1, "Choose file", , 0)
Set wb = Workbooks.Open(fn)
Set ws = wb.Sheets("Sheet1")
n = Split(fn, "\")                              ' to get the other workbook's name
ThisWorkbook.Sheets("Sheet1").Activate
fc = "a:a"                                      ' column to be used on this workbook
For i = 1 To Evaluate("=match(lookup(2,1/(" & fc & "<>"""")," & fc & ")," & fc & ",0)")
    j = Evaluate("=address(match(a" & i & "," & "'[" & n(UBound(n)) & "]Sheet1'!a:a,0),1)")
    If Not IsError(j) Then
        ws.Range(j).Interior.Color = RGB(250, 5, 5)
        ThisWorkbook.Sheets("Sheet1").Range("a" & i).Interior.Color = RGB(250, 5, 5)
    End If
Next
End Sub