Private Sub Worksheet_Activate()
Dim annee%, mois%, semaine, coul1%, coul2%, derlig&, tablo(), w As Worksheet, c As Range, c1 As Range, i&
annee = 2020 'à adapter
mois = 4 'avril,à adapter
semaine = Array("S14", "S15", "S16", "S17", "S18") 'à adapter
coul1 = 15 'gris 25%
coul2 = 40 'brun
If FilterMode Then ShowAllData 'si la feuille est filtrée
derlig = Range("A" & Rows.Count).End(xlUp).Row
ReDim tablo(1 To derlig - 7, 1 To 2) 'matrice, plus rapide
For Each w In Sheets(semaine)
For Each c In w.Rows("8:" & derlig).SpecialCells(xlCellTypeFormulas)
Set c1 = w.Cells(6, c.Column).MergeArea(1) 'date en ligne 6
If Year(c1) = annee And Month(c1) = mois Then
If c.DisplayFormat.Interior.ColorIndex = coul1 Then i = c.Row - 7: tablo(i, 1) = tablo(i, 1) + TimeValue(Mid(c, 3))
If c.DisplayFormat.Interior.ColorIndex = coul2 Then i = c.Row - 7: tablo(i, 2) = tablo(i, 2) + TimeValue(Mid(c, 3))
End If
Next c
Next w
'---restitution---
[O8].Resize(UBound(tablo), 2) = tablo
End Sub