Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim Plage As Range
Dim Cel As Range
With Worksheets("Feuille 1")
Set Plage = .Range(.Cells(2, 13), .Cells(.Rows.Count, 13).End(xlUp))
For Each Cel In Plage
If Application.CountIf(.Range("M2", .Cells(Cel.Row, 13)), Cel.Value) = 2 Then
MsgBox "Attention, la donnée '" & Cel.Value & "' est en doublon," _
& " veuillez vérifier que votre saisie située en '" & Cel.Address(0, 0) _
& "' est conforme au dossier client. "
End If
If Application.CountIf(Plage, Cel.Value) >= 2 Then
Cel.Interior.ColorIndex = 3
End If
Next Cel
End With
With Worksheets("Feuille 1")
Set Plage = .Range(.Cells(2, 12), .Cells(.Rows.Count, 12).End(xlUp))
End With
For Each Cel In Plage
If Application.CountIf(Plage, Cel.Value) > 1 Then
MsgBox "Attention, la valeur '" & Cel.Value & "' est en doublon," _
& " veuillez resaisir le champ situé en '" & Cel.Address(0, 0) _
& "' avant de finaliser la saisie !"
Cel.Interior.ColorIndex = 3
End If
Next Cel
End Sub