Modify Macro to use FDIALOG to select Workbook 2 in Excel

[adibakale]

I have a macro that compares 2 excel files and highlights duplicates in Column A of both workbooks.

I want to run the macro from workbook 1, then use fdialog too choose Workbook 2 that i want to compare and highlight duplicates.

I am fairly new to VBA and cannot figure this out, Any help is greatly appreciated.

---

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

 

[Aaron Blood]

Good question...

From the looks of your code, I think you just need the extra bit below to see how to pull the filename with the dialog and then assign it to the WB2 object:

Sub PickaFile()

    Dim MyPick As String
    With Application.FileDialog(msoFileDialogFilePicker)
        .Show
        MyPick = .SelectedItems(1)
    End With
    

    Dim WB2 As Workbook
    Set WB2 = Workbooks.Open(MyPick)
    

End Sub