Hi,

I need to create a macro which will search all
occurrences of "item1" in column D and copy the values to
the next cells in column B. The worksheet event below is
exactly what I need

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count 1 Then Exit Sub

If Intersect(Target, Me.Range("a:a")) Is Nothing Then
Exit Sub

On Error GoTo errHandler:

With Target
If IsNumeric(.Value) Then
If .Value 5 Then
Application.EnableEvents = False
.Offset(0, 3).Value="item1"
End If
End If
End With

errHandler:
Application.EnableEvents = True

End Sub

BUT there is an insoluble problem with it because
Worksheet Change event doesn't recognize a change by
pasting a value. So i need to change the above event to
code which will recognize change by pasting the value.
Any help is highly appreciated.