Excel file hangs when code for checking data added

[Dean Zuki]

Hi Expert,

Please help to see below code. My Excel file hangs when code added for checking whether data exist at sheet "Registration List 2018". MsgBox dialog box keep on running. If I remove this line of code

(If Application.WorksheetFunction.CountIf(Sheets("Search").Range("B:B"), Target.Value) + Application.WorksheetFunction.CountIf(Sheets("Registration List 2018").Range("A:A"), Target.Value) = 0 Then
 MsgBox "No record found, please contact HR Personnel"
    Target.ClearContents
    Else
 'Nothing)

the system work fine.

Private Sub Worksheet_Change(ByVal Target As Range)
   
 If Target.Address = "$B$5" Then
 If Application.WorksheetFunction.CountIf(Sheets("Search").Range("B:B"), Target.Value) + Application.WorksheetFunction.CountIf(Sheets("Registration List 2018").Range("A:A"), Target.Value) = 0 Then
 MsgBox "No record found, please contact HR Personnel"
    Target.ClearContents
    Else
 'Nothing
 
 If Application.WorksheetFunction.CountIf(Sheets("Search").Range("B:B"), Target.Value) + Application.WorksheetFunction.CountIf(Sheets("Reported").Range("A:A"), Target.Value) > 1 Then
 MsgBox "You have entered a duplicate number, please try again"
    Target.ClearContents
    Else
 'Nothing
 
 If Len(Target.Value) = 12 Then
      Call Copydata
    End If
End If
End If
End If

End Sub