Option Explicit
'
Function CYC(ByVal D As Range) As String
Const Z = "NN--NNN--NN---NNNN----------NN--NNN--NN----------"
If Not IsDate(D.Value) Then Exit Function
CYC = Mid$(Z, (D.Value - (Application.Caller.Row - D.Row) * 7 - 2) Mod 49 + 1, 1)
End Function
'
Sub Installation()
Dim N&, L&, PLg As Range
Application.Goto Feuil1.[A1]
Cells.FormatConditions.Delete
For N = 1 To 12
If N = 1 Then
L = 11 ' Ne pouviez vous vraiment pas vous arranger pour que janvier soit en ligne 14 ?
Else ' Touts les autres mois sont basés sur la 14 :
L = 14 + (N - 1) * 11
End If
Set PLg = [B:AF].Rows(L).Resize(11)
If N = 2 Then
Cells(L, "AD").FormulaR1C1 = "=IF(MONTH(SLF)=MONTH(RC2),SLF,"""")"
Cells(L + 1, "AD").FormulaR1C1 = "=IF(MONTH(SLF)=MONTH(R" & L & "C2),_SLF1,"""")"
End If
PLg.Rows(3).Resize(7).FormulaR1C1 = "=CYC(R" & L & "C)"
With PLg.Resize(11).FormatConditions.Add(Type:=xlExpression, _
Formula1:="=OU(JOURSEM(B$" & L & ";2)=7;ESTNUM(EQUIV(B$" & L & ";Ferie;0)))")
.Interior.Color = RGB(199, 255, 151): .Font.Color = RGB(186, 0, 0)
.StopIfTrue = False
End With
Next N
End Sub