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 77
|
Sub weekend() 'en fonction de l'année, griser les fériés et les weekends
ActiveSheet.Unprotect Password:="admin"
'j'ai mis AG27 car, dans mon cas j'ai laissé une ligne entre chaque mois pour fluidifier la feuille (visuellement).
'lors du changement de l'année, ceci efface tous le contenus des cellules
ActiveSheet.Range("C5:AG27").ClearContents
ActiveSheet.Range("C5:AG27").Interior.ColorIndex = xlNone
Dim jour As Variant
Dim i As Integer
Dim x As Integer
For i = 1 To 33 ' pour les 31 jours - les 2 premières cellules sont vides
jour = Cells(3, i).Value 'jour va prendre a chaque fois la valeur de la cellule C3, D3 et ainsi de suite jusqu'au AG3
For x = 1 To 366 Step 1 'pour les fériés et les weekends
If Worksheets("Fériés").Range("A" & x) = "janvier" & " " & jour Or Worksheets("weekend").Range("A" & x) = "dimanche" & " " & "janvier" & " " & jour Or Worksheets("weekend").Range("A" & x) = "samedi" & " " & "janvier" & " " & jour Then
Cells(5, i).Value = "x" ' mettre x si férié/weekend
Cells(5, i).Interior.ColorIndex = 16 'mettre du GRI si férié/weekend
End If
If Worksheets("Fériés").Range("A" & x) = "février" & " " & jour Or Worksheets("weekend").Range("A" & x) = "dimanche" & " " & "février" & " " & jour Or Worksheets("weekend").Range("A" & x) = "samedi" & " " & "février" & " " & jour Then
Cells(7, i).Value = "x"
Cells(7, i).Interior.ColorIndex = 16
End If
If Worksheets("Fériés").Range("A" & x) = "mars" & " " & jour Or Worksheets("weekend").Range("A" & x) = "dimanche" & " " & "mars" & " " & jour Or Worksheets("weekend").Range("A" & x) = "samedi" & " " & "mars" & " " & jour Then
Cells(9, i).Value = "x"
Cells(9, i).Interior.ColorIndex = 16
End If
If Worksheets("Fériés").Range("A" & x) = "avril" & " " & jour Or Worksheets("weekend").Range("A" & x) = "dimanche" & " " & "avril" & " " & jour Or Worksheets("weekend").Range("A" & x) = "samedi" & " " & "avril" & " " & jour Then
Cells(11, i).Value = "x"
Cells(11, i).Interior.ColorIndex = 16
End If
If Worksheets("Fériés").Range("A" & x) = "mai" & " " & jour Or Worksheets("weekend").Range("A" & x) = "dimanche" & " " & "mai" & " " & jour Or Worksheets("weekend").Range("A" & x) = "samedi" & " " & "mai" & " " & jour Then
Cells(13, i).Value = "x"
Cells(13, i).Interior.ColorIndex = 16
End If
If Worksheets("Fériés").Range("A" & x) = "juin" & " " & jour Or Worksheets("weekend").Range("A" & x) = "dimanche" & " " & "juin" & " " & jour Or Worksheets("weekend").Range("A" & x) = "samedi" & " " & "juin" & " " & jour Then
Cells(15, i).Value = "x"
Cells(15, i).Interior.ColorIndex = 16
End If
If Worksheets("Fériés").Range("A" & x) = "juillet" & " " & jour Or Worksheets("weekend").Range("A" & x) = "dimanche" & " " & "juillet" & " " & jour Or Worksheets("weekend").Range("A" & x) = "samedi" & " " & "juillet" & " " & jour Then
Cells(17, i).Value = "x"
Cells(17, i).Interior.ColorIndex = 16
End If
If Worksheets("Fériés").Range("A" & x) = "août" & " " & jour Or Worksheets("weekend").Range("A" & x) = "dimanche" & " " & "août" & " " & jour Or Worksheets("weekend").Range("A" & x) = "samedi" & " " & "août" & " " & jour Then
Cells(19, i).Value = "x"
Cells(19, i).Interior.ColorIndex = 16
End If
If Worksheets("Fériés").Range("A" & x) = "septembre" & " " & jour Or Worksheets("weekend").Range("A" & x) = "dimanche" & " " & "septembre" & " " & jour Or Worksheets("weekend").Range("A" & x) = "samedi" & " " & "septembre" & " " & jour Then
Cells(21, i).Value = "x"
Cells(21, i).Interior.ColorIndex = 16
End If
If Worksheets("Fériés").Range("A" & x) = "octobre" & " " & jour Or Worksheets("weekend").Range("A" & x) = "dimanche" & " " & "octobre" & " " & jour Or Worksheets("weekend").Range("A" & x) = "samedi" & " " & "octobre" & " " & jour Then
Cells(23, i).Value = "x"
Cells(23, i).Interior.ColorIndex = 16
End If
If Worksheets("Fériés").Range("A" & x) = "novembre" & " " & jour Or Worksheets("weekend").Range("A" & x) = "dimanche" & " " & "novembre" & " " & jour Or Worksheets("weekend").Range("A" & x) = "samedi" & " " & "novembre" & " " & jour Then
Cells(25, i).Value = "x"
Cells(25, i).Interior.ColorIndex = 16
End If
If Worksheets("Fériés").Range("A" & x) = "décembre" & " " & jour Or Worksheets("weekend").Range("A" & x) = "dimanche" & " " & "décembre" & " " & jour Or Worksheets("weekend").Range("A" & x) = "samedi" & " " & "décembre" & " " & jour Then
Cells(27, i).Value = "x"
Cells(27, i).Interior.ColorIndex = 16
End If
Next x
Next i
ActiveSheet.Protect Password:="admin"
End Sub |
Partager