Absolutely possible. 
Code modifications:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim sErrorMsg As String
'Check if value = "CLOSE"
If Target.Value = "CLOSE" Then
'Turn off events to prevent recursive calls
Application.EnableEvents = False
'Copy row to new worksheet
Select Case check_condition_before(Target)
Case Is = "Missing-Vessel"
sErrorMsg = "the vessel name"
Case Is = "Missing-Pack"
sErrorMsg = "the pack details"
Case Is = "Missing-All"
sErrorMsg = "both the vessell name and pack details"
End Select
End If
If sErrorMsg = vbNullString Then
'do nothing as all is okay
Else
MsgBox "Sorry, but you are missing " & sErrorMsg & "!", vbOKOnly + vbCritical
Target.ClearContents
End If
Application.EnableEvents = True
End Sub
Code:
Function check_condition_before(rngTarget As Range) As String
Set rngvalidate = Range("E" & rngTarget.Row & ":F" & rngTarget.Row)
Select Case Application.WorksheetFunction.CountA(rngvalidate)
Case Is = 2
check_condition_before = "Valid"
Case Is = 1
If Range("E" & rngTarget.Row).Value = vbNullString Then
check_condition_before = "Missing-Pack"
Else
check_condition_before = "Missing-Vessel"
End If
Case Else
check_condition_before = "Missing-All"
End Select
End Function
Bookmarks