Vba to find and highlight matches in excel

[Bammo]

Hi Everyone,
i have two excel spreadsheets (spreadsheet 1&2). Spreadsheet 1 contains over 2000 rows of names companies and spreadsheet 2 is database dump of various values and names

I am looking for a macro that can find/search each value in spreadsheet 1 and see if there is a match or near match in spreadsheet 2 as well as highlight the match in spreadsheet 1. Alternatively if there is a match in spreadsheet2 it could return locations in a separate column.

i hope this makes sense.
Please any help will be greatly appreciated

Many thanks

 

[patel45]

a possible solution (very slow)

Sub FindDupSheet1_2()
Dim cell As Range, cella As Range, rng As Range, srng As Range
Set rng = Sheets(2).UsedRange
Set srng = Sheets(1).UsedRange
For Each cell In rng
    For Each cella In srng
    If cella = cell Then
    cella.Interior.ColorIndex = 6
'    cella.AddComment.Text Text:="duplicate value"
    End If
    Next cella
Next cell
End Sub

for faster have a look at http://www.mrexcel.com/forum/excel-...are-two-columns-show-differences-another.html

 

[patel45]

better solution

Sub CompareRanges()
LR1 = Worksheets(1).Cells(Rows.Count, "A").End(xlUp).Row
LR2 = Worksheets(2).Cells(Rows.Count, "A").End(xlUp).Row
Set rng1 = Worksheets(1).Range("A1:A" & LR1)
Set rng2 = Worksheets(2).Range("A1:A" & LR1)
    For Each rCell In rng1
        rCell.Interior.ColorIndex = xlNone
        rCell.Validation.Delete
        result = WorksheetFunction.CountIf(rng2, rCell)
        If result > 0 Then rCell.Interior.Color = vbGreen
    Next
End Sub