Try this...
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rgClear As Range
Dim MsgBoxClick As VbMsgBoxResult
Dim i As Long
Application.EnableEvents = False
Set rgClear = Union(Range("B8:B2000"), Range("C8:C2000"), Range("E8:E2000").Resize(, 7), Range("M8:M2000").Resize(, 3))
If Target.Address = Range("Choice_SE").Address Then 'k4
MsgBoxClick = MsgBox("Changing the ERP will clear all the details you entered." _
& vbCrLf & vbCrLf & "Enter/Select New co.code (Col J) and Local Account (Col G) to start filling the template." _
& vbCrLf & vbCrLf & "Are you sure you want to continue?", vbYesNo, "Clear Journal Detail")
If MsgBoxClick = vbYes Then rgClear.ClearContents
End If
' /*********************Company Code*********************/
If Target.Column = 10 Then
For i = 1 To Target.Cells.Count
Target.Cells(i, 1).Offset(, -8) = IIf(Target.Cells(i, 1).Value <> "", _
Application.VLookup( _
Target.Cells(i, 1).Value & Range("K4").Value, _
Sheets("Coco").Range("A:D"), _
4, _
0), _
"")
Next i
End If
Application.EnableEvents = True
End Sub
Bookmarks