Scottmk

try



Private Sub Worksheet_Change(ByVal Target As Range)
Dim v(1) As Variant
Dim lRow As Long

If Target.Address = "$G$4" Then
Application.EnableEvents = False
v(1) = Target.Address
'get next free column in row 5
lRow = Cells(Rows.Count, "h").End(xlUp).Row
' clear used cell row 5 to ??? column H
If lRow 5 Then
Cells(5, "h").Resize(lRow - 4).ClearContents
Else
Cells(5, "h").ClearContents
End If
' find & list all matches
Do
v(0) = Cells.Find(Target, after:=Range(v(1))).Address
If v(0) = Target.Address Or v(0) = v(1) Then
Exit Do
End If
lRow = Cells(Rows.Count, "h").End(xlUp).Row + 1
If lRow < 5 Then
lRow = 5
End If
Cells(lRow, "h").Value = Range(v(0)).Offset(, 1).Value
v(1) = v(0)
Loop
End If
Application.EnableEvents = True
End Su

--
Message posted from http://www.ExcelForum.com