Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect([K2:W2], Target) Is Nothing Then
D = 7 * Target + DateSerial(2011, 1, 3) - Weekday(DateSerial(2011, 1, 3)) - 4 ', rien = samedi, -1 = vendredi, -2 = jeudi, -3 = mercredi, -4 = mardi, -5 = lundi, -6 = dimanche
If Target.Comment Is Nothing Then Target.AddComment
Target.Comment.Text Text:=Format(D, "dddd dd mmm yyyy")
Target.Comment.Shape.TextFrame.AutoSize = True
Next C
End If
'ICI ANNEE 2011 ET 2012
If Not Intersect([X2:Y2,AE2:AS2], Target) Is Nothing Then
D = 7 * Target + DateSerial(2012, 1, 3) - Weekday(DateSerial(2012, 1, 3)) - 4 ', rien = samedi, -1 = vendredi, -2 = jeudi, -3 = mercredi, -4 = mardi, -5 = lundi, -6 = dimanche
If Target.Comment Is Nothing Then Target.AddComment
Target.Comment.Text Text:=Format(D, "dddd dd mmm yyyy")
Target.Comment.Shape.TextFrame.AutoSize = True
Next C
End If
End Sub
For Each C In ActiveSheet.Comments
C.Shape.AutoShapeType = msoShapeRoundedRectangle 'Forme rectangle
C.Shape.OLEFormat.Object.Font.Size = 15 'Taille police
C.Shape.TextFrame.Characters.Font.ColorIndex = 5 'Couleur police bleu
C.Shape.OLEFormat.Object.Font.Bold = True 'Police en gras
C.Shape.OLEFormat.Object.Font.Name = "Garamond" ' type de police
C.Shape.OLEFormat.Object.Font.Italic = True 'texte italic
C.Shape.Width = 200 'Largeur
C.Shape.Height = 30 'Hauteur
C.Shape.Fill.ForeColor.SchemeColor = 52 'Couleur de fond
C.Shape.TextFrame.HorizontalAlignment = xlHAlignCenter ' texte au centre
Next C
c.Shape.OLEFormat.Object.Text = WorksheetFunction.Proper(c.Shape.OLEFormat.Object.Text)
With c.Shape.OLEFormat.Object
.Text = StrConv(.Text, vbProperCase)
End With