Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Column <> 1 And .Column <> 3 Then Exit Sub
If .Count > 1 Then Exit Sub
If .Value = "" Or [B2] = "" Then Exit Sub
If WorksheetFunction.CountIf(Sheets([B2].Value).Range("A:B"), Target) = 0 Then Exit Sub
MsgBox "le n° " & target & " est déjà saisi.", 48, "pas accepté :"
Application.EnableEvents = 0: .Value = ""
Application.EnableEvents = -1
End With
End Sub