1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76
| Sub test()
Dim M1 As Range
Dim M2 As Range
Dim M3 As Range
Dim M4 As Range
Dim M5 As Range
Dim M6 As Range
Dim M7 As Range
Dim M8 As Range
Dim M9 As Range
Dim M10 As Range
Dim M11 As Range
Dim M12 As Range
Dim F As Worksheet
Dim R As Range
Dim X As String
Application.ScreenUpdating = False
With Sheets("Agenda Réunions CNCH")
.Range("B9:H13").Interior.ColorIndex = xlColorIndexNone
.Range("K9:Q13").Interior.ColorIndex = xlColorIndexNone
.Range("U9:AA13").Interior.ColorIndex = xlColorIndexNone
.Range("B19:H23").Interior.ColorIndex = xlColorIndexNone
.Range("K19:Q23").Interior.ColorIndex = xlColorIndexNone
.Range("U19:AA24").Interior.ColorIndex = xlColorIndexNone
.Range("B32:H36").Interior.ColorIndex = xlColorIndexNone
.Range("K32:Q36").Interior.ColorIndex = xlColorIndexNone
.Range("U32:AA36").Interior.ColorIndex = xlColorIndexNone
.Range("B46:H50").Interior.ColorIndex = xlColorIndexNone
.Range("K46:Q50").Interior.ColorIndex = xlColorIndexNone
.Range("U46:AA50").Interior.ColorIndex = xlColorIndexNone
.Cells.ClearComments
End With
For Each F In Worksheets
If F.Name <> "Agenda Réunions CNCH" Then
Set M1 = F.Range("B9:H13")
Set M2 = F.Range("K9:Q13")
Set M3 = F.Range("U9:AA13")
Set M4 = F.Range("B19:H23")
Set M5 = F.Range("K19:Q23")
Set M6 = F.Range("U19:AA24")
Set M7 = F.Range("B32:H36")
Set M8 = F.Range("K32:Q36")
Set M9 = F.Range("U32:AA36")
Set M10 = F.Range("B46:H50")
Set M11 = F.Range("K46:Q50")
Set M12 = F.Range("U46:AA50")
Set Plage = Application.Union(M1, M2, M3, M4, M5, M6, M7, M8, M9, M10, M11, M12)
For Each R In Plage
If R.Interior.ColorIndex <> xlColorIndexNone Then
If Not R.Comment Is Nothing Then
X = R.Comment.Text
Else
X = "Pas d'objet pour cette réunion"
End If
lig = R.Row
Col = R.Column
If Sheets("Agenda Réunions CNCH").Cells(lig, Col).Interior.ColorIndex <> xlColorIndexNone Then
MsgBox ("Vous avez deux réunion en même date !!! merci de vérifier")
F.Select
R.Select
Exit Sub
Else
Sheets("Agenda Réunions CNCH").Cells(lig, Col).Interior.ColorIndex = R.Interior.ColorIndex
If IsNull(X) Then X = "Pas d'objet pour cette réunion"
With Sheets("Agenda Réunions CNCH").Cells(lig, Col).AddComment
.Shape.Placement = xlFreeFloating
.Shape.TextFrame.AutoSize = True
.Text Text:=X
End With
End If
End If
Next R
End If
Next F
Application.ScreenUpdating = True
End Sub |
Partager