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

--
Regards,
Tom Ogilvy



"louie" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>

[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.