Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Not Intersect(Target, Range("C:C")) Is Nothing Then
Select Case Target.Value
Case ""
Case Else
For i = 1 To ThisWorkbook.Sheets.Count
Select Case Application.WorksheetFunction.CountIf(Sheets(i).[C:C], Target)
Case 0
Case 1
If Sheets(i).Name <> ActiveSheet.Name Then
GoTo a_modifier
End If
Case Else
GoTo a_modifier
End Select
Next
End Select
Exit Sub
a_modifier:
myPrompt = " !!! Cette donnée existe déjà dans la feuille " & Sheets(i).Name & " Ligne " & Sheets(i).Cells.Find(What:=Target.Value, After:=Sheets(i).Cells(1, 1), LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Row & " colonne " & Sheets(i).Cells.Find(What:=Target.Value, After:=Sheets(i).Cells(1, 1), LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Column
myDefault = Target
myInput = InputBox(Prompt:=myPrompt, Default:=myDefault, Title:="Attention")
Target = myInput
End If
End Sub