Scottmk

Try this code




Private Sub Worksheet_Change(ByVal Target As Range)
Dim v(1) As Variant
Dim iCol As Integer

If Target.Address = "$G$4" Then
Application.EnableEvents = False
v(1) = Target.Address
'get next free column in row 5
iCol = Cells(5, Columns.Count).End(xlToLeft).Column
' clear used cell row 5 column H to ???
If iCol 8 Then
Cells(5, "h").Resize(, iCol - 7).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
iCol = Cells(5, Columns.Count).End(xlToLeft).Column + 1
If iCol < 8 Then
iCol = 8
End If
Cells(5, iCol).Value = Range(v(0)).Offset(, 1).Value
v(1) = v(0)
Loop
End If
Application.EnableEvents = True
End Sub



If code crashes you may need to run this code to enable change event.


Sub ddd()
Application.EnableEvents = True
End Su

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