Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ENDEND
Application.EnableEvents = True
If Len(Target) <= 0 Then
Exit Sub
End If
Dim Haz(30) As String
If Target.Column = 2 Then ' Do it when it is Column number 2
If Target.Row > 3 Then ' Do it when start from Row number 4
If Len(Target.Value) > 0 Then
Sheets("Form").Select
Sheets("Hazard").Activate
Sheets("Hazard").Range("A2").Select
Do While Len(ActiveCell.Value) > 0
If ActiveCell.Value = Target.Value Then
HazNum = ActiveCell.Offset(0, 1).Value
For N = 0 To HazNum - 1
Haz(N) = ActiveCell.Offset(0, N + 2)
Next N
Exit Do
End If
ActiveCell.Offset(1, 0).Select
Loop
Sheets("Form").Select
Target.Select
If HazNum > 0 Then
For N = 0 To HazNum - 1
ActiveCell.Offset(N, 5).Value = Haz(N) 'Copy to the Column 'G' (5)
Next N
ActiveCell.Offset(HazNum + 1, 0).Select
End If
End If
End If
End If
Exit Sub
ENDEND:
Exit Sub
End Sub