Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long
If (Not Intersect(Range("B4"), Target) Is Nothing) And (Range("B4").Value <> "") Then
ActiveSheet.Unprotect
Range("B4").ClearComments
For i = 1 To Sheets("Données").Range("C65536").End(xlUp).Row
If Target.Value = Sheets("Données").Cells(i, 3).Value Then
With Range("B4")
ActiveSheet.Unprotect
On Error Resume Next
.AddComment
.Comment.Text Text:=.Comment.Text & _
"Il existe déjà un contrat n° " & .Value & " " & Chr(10)
[COLOR=red]With Selection.Font
.FontStyle = "Gras"
.Size = 10
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.ReadingOrder = xlContext
.Orientation = xlHorizontal[/COLOR]
[COLOR=red] End With
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 51
Selection.ShapeRange.Line.Weight = 1.5
[/COLOR] .Comment.Visible = True
End With
End If
Next i
End If
ActiveSheet.Protect
End Sub