Private Sub Worksheet_Calculate()
Dim x, fund As Range
Dim rng As Range: Set rng = Range("b11:d21")
For Each x In rng.Cells
If x <> "" Then
If WorksheetFunction.CountIf(Intersect(ActiveSheet.UsedRange, x.EntireColumn), x.Value) > 1 Then
Set fund = Intersect(x.EntireColumn, Range("B2:D9")).Find(x.Value, LookIn:=xlValues, Lookat:=xlWhole)
If Not fund Is Nothing Then
MsgBox "Person wurde schon eingeplant"
Application.EnableEvents = False
fund = ""
Application.EnableEvents = True
End If
End If
End If
Next
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B2:D9")) Is Nothing Then
If Target <> "" Then
If WorksheetFunction.CountIf(Intersect(ActiveSheet.UsedRange, Target.EntireColumn), Target.Value) > 1 Then
MsgBox "Person wurde schon ausgewählt oder fehlt"
Application.EnableEvents = False
Target = ""
Application.EnableEvents = True
End If
End If
End If
End Sub