untested, but maybe something like
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim C As Range
'limit to single cell
If Target.Count > 1 Then Exit Sub
'monitor specific range
If Intersect(Target, Range("H4:H5000")) Is Nothing Then Exit Sub
'dealing with the entry
If Target.Value <> "" Then
'search this sheet
With Sheets("Clipsal Customer")
Set C = .Range("H4:H" & Target.Row - 1).Find(What:=Target.Value, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
End With
If C Is Nothing Then ' meaning it was NOT found, so...
' search the other sheet
With Sheets("whatever its name is")
Set C = .Range("whatever the range is").Find(What:=Target.Value, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
End With
End If
If Not C Is Nothing Then
'copy from C.row to Target.row
Application.EnableEvents = False
Target.Offset(, 1).Resize(, 4).Value = C.Offset(, 1).Resize(, 4).Value
Target.Offset(, 17).Resize(, 9).Value = C.Offset(, 17).Resize(, 9).Value
Application.EnableEvents = True
Else
MsgBox Target.Value & " was not found."
End If
End If
End Sub