populate message box if target bigger than 3 times for the same name

[tubrak]

Hi experts ,
I want when I repeat the same name in column C bigger than three times , then should populate message box " you can't repeat more than three times" and clear target and if not , then should pops message " you still can add within allowed limiting " and continue add repeating name .
I have this code but doesn't work as what I want it .

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If Target.Column = 3 And Target.Row > 1 Then
Application.DisplayAlerts = False
With ThisWorkbook.Sheets("Sheet1")
    lr = .Cells(.Rows.Count, "C").End(xlUp).Row
   ' If Target.Value = "" Then Exit Sub
    If .Evaluate("=Max(countif(C2:C" & lr & ",C2:C" & lr & "))") > 3Then
    
       MsgBox "you  can't  repeat more than three times ! " & Target.Value
       Target.Value = ""
       Exit Sub
      
       Else
        MsgBox "you  still can  add within allowed limiting !"
    End If
     Application.DisplayAlerts = True
     
End With
End If
End Sub

I look forward from experts to fix it.

 

[Joe4]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim lr As Long
    Dim rng As Range

    If Target.CountLarge > 1 Then Exit Sub
    
    If Target.Column = 3 And Target.Row > 1 Then
        lr = Cells(Rows.Count, "C").End(xlUp).Row
        Set rng = Range("C2:C" & lr)
        If Application.WorksheetFunction.CountIf(rng, Target) > 3 Then
            Application.EnableEvents = False
            Target.ClearContents
            Application.EnableEvents = True
            MsgBox "You can't repeat more than three times!" & Target.Value
        Else
            MsgBox "You still can add within allowed limiting!"
        End If
    End If

End Sub

 

[tubrak]

perfect !
thanks for your updating .

 

[Joe4]

You are welcome.
Glad I was able to help!