VBA

Private Sub Worksheet_Change(ByVal Target As Range)
Dim var As Variant
Dim iRow As Integer
Dim sort As String
If Target.Column <> 4 Then Exit Sub
Application.EnableEvents = False
On Error GoTo ERRORHANDLER
If IsEmpty(Target) Then
Target.Offset(0, 1).ClearContents
Exit Sub
End If
With Range("ip5400:iv5476")
var = Application.Match(Target.Value, .Columns(1), 0)
If Not IsError(var) Then
Target.Offset(0, 1).Value = .Cells(var, 2).Value
Target.Offset(0, 7).Value = .Cells(var, 3).Value
Target.Offset(0, 33).Value = .Cells(var, 4).Value * Target.Offset(0, 18).Value
Target.Offset(0, 34).Value = .Cells(var, 5).Value * Target.Offset(0, 18).Value
Target.Offset(0, 35).Value = .Cells(var, 6).Value * Target.Offset(0, 18).Value
Target.Offset(0, 36).Value = .Cells(var, 7).Value * Target.Offset(0, 18).Value
Else
MsgBox "!!! ERROR!!!"
End If
End With
ERRORHANDLER:
Application.EnableEvents = True
End Sub