Matching Values in Columns problems

[louie]

Hello,
I have written a macro that matches values in two columns and highlights the
matches. The problem arises in a loop that is searching in one column has
no match in the other. The code generates an error. Any input appreciated,
code follows.

Sub Match()
'Compares values in 2 columns and highlights if the same

For Nextloop = 1 To 50
ActiveCell.Offset(1, 0).Activate
i = 1
Do Until ActiveCell.Value = Range("b1").Offset(i, 0)
i = i + 1
Loop
If ActiveCell.Value = Range("b1").Offset(i, 0) Then
Range("b1").Offset(i, 0).Cells.Interior.ColorIndex = 17
If IsEmpty(ActiveCell) Then
Exit Sub
End If
End If
Next Nextloop
End Sub

 

[Tom Ogilvy]

Sub Match()
Dim rng as Range, lastRow as Long
Dim i as Long
'Compares values in 2 columns and highlights if the same
lastRow = cells(rows.count,"B").End(xlup).Row
set rng = Range(ActiveCell, ActiveCell.End(xldown))
for i = 1 to lastrow
if Application.Countif(rng,cells(i,"B")) > 0 then
Cells(i,"B").Interior.ColorIndex = 17
Next i
End Sub

 

[louie]

Thanks Tom, your code works great. I will study it to help me learn better
ways to programatically make life with excel easier. I added End If before
Next i to complete your code in case anyone copies it for use.