Private Sub Worksheet_Change(ByVal Target As Range)
Dim dat$, i&, j%, liste1$, liste2$, x, y, a&, b&
'---cellule I2---
If Not Intersect(Target, [I2]) Is Nothing Then
dat = "1/1/" & [I2]
[E2,G2] = ""
Application.ScreenUpdating = False
Application.EnableEvents = False
For i = 11 To 187 Step 16
If IsDate(dat) Then
If i = 11 Then Cells(i, 3) = CDate(dat) Else Cells(i, 3) = Application.Max(Rows(i - 16)) + 1
Cells(i, 3).Resize(, 31).DataSeries Type:=xlChronological, Date:=xlDay
For j = 29 To 31 'contrôle des 3 derniers jours
If Day(Cells(i, j + 2)) < 4 Then Cells(i, j + 2) = ""
Next j
Cells(i - 3, 3).Resize(, 31) = "=IF(COUNTIF(RC2:RC[-1],ISOWEEKNUM(R[3]C))+(R[3]C=""""),"""",ISOWEEKNUM(R[3]C))"
Cells(i - 3, 3).Resize(, 31) = Cells(i - 3, 3).Resize(, 31).Value 'supprime les formules
liste1 = liste1 & "," & Format(Cells(i, 3), "dd.mm.yyyy")
liste2 = liste2 & "," & Format(Application.Max(Rows(i)), "dd.mm.yyyy")
Else
Union(Cells(i, 3).Resize(, 31), Cells(i - 3, 3).Resize(, 31)) = "" 'RAZ
End If
Next i
[E2,G2].Validation.Delete
If IsDate(dat) Then
[E2].Validation.Add xlValidateList, Formula1:=Mid(liste1, 2)
[G2].Validation.Add xlValidateList, Formula1:=Mid(liste2, 2)
End If
Application.EnableEvents = True
End If
'---cellules E2 et G2---
If Intersect(Target, [E2,G2]) Is Nothing Then Exit Sub
x = Replace([E2].Value, ".", "/"): y = Replace([G2], ".", "/")
If Not IsDate(x) Or Not IsDate(y) Then Rows.Hidden = False: Exit Sub
x = CLng(CDate(x)): y = CLng(CDate(y))
For i = 11 To 187 Step 16
If IsNumeric(Application.Match(x, Rows(i), 0)) Then a = i
If IsNumeric(Application.Match(y, Rows(i), 0)) Then b = i
Next i
Application.ScreenUpdating = False
Rows("8:198").Hidden = True
Range(Rows(a - 3).Resize(15), Rows(b - 3).Resize(15)).Hidden = False
End Sub