Compare and highlight duplicates in two workbooks
 

Hi, how do I compare column A in Workbook 1 with column A in Workbook 2 and
highlight the entries in Workbook 1 that are found in Workbook 2 ?

Regards
Dylan

Compare and highlight duplicates in two workbooks
 

Hi Dylan

The code below would be one way of doing it or at least give you an
idea where to start.

Option Explicit

Sub FindAndMarkDups()
Dim WkBk1 As Workbook
Dim WkBk2 As Workbook
Dim i As Integer
Dim SearchFor As String
Dim FoundCell

Set WkBk1 = Workbooks("Book1.xls")
Set WkBk2 = Workbooks("Book2.xls")

With WkBk1.Sheets("Sheet1")

..Activate

For i = 1 To [A65535].End(xlUp).Row

SearchFor = .Cells(i, 1).Value

WkBk2.Sheets("Sheet1").Activate

Set FoundCell = [A:A].Find(What:=SearchFor, LookAt:=xlWhole)

If Not FoundCell Is Nothing Then

..Cells(i, 1).Interior.Color = vbRed

End If

Next

..Activate

End With

End Sub

Hope this helps

Steve

Compare and highlight duplicates in two workbooks
 

Thanks Steve, it works perfectly.

Dylan

"Incidental" wrote:

Hi Dylan

The code below would be one way of doing it or at least give you an
idea where to start.

Option Explicit

Sub FindAndMarkDups()
Dim WkBk1 As Workbook
Dim WkBk2 As Workbook
Dim i As Integer
Dim SearchFor As String
Dim FoundCell

Set WkBk1 = Workbooks("Book1.xls")
Set WkBk2 = Workbooks("Book2.xls")

With WkBk1.Sheets("Sheet1")

..Activate

For i = 1 To [A65535].End(xlUp).Row

SearchFor = .Cells(i, 1).Value

WkBk2.Sheets("Sheet1").Activate

Set FoundCell = [A:A].Find(What:=SearchFor, LookAt:=xlWhole)

If Not FoundCell Is Nothing Then

..Cells(i, 1).Interior.Color = vbRed

End If

Next

..Activate

End With

End Sub

Hope this helps

Steve