Private Sub Worksheet_Change(ByVal Target As Range)
Dim jour, deb As Range, fin As Range, h&, c As Range, i As Variant, j As Variant, Nhotesse&, cc As Range
Application.ScreenUpdating = False
Application.EnableEvents = False 'désactive les évènements
On Error GoTo 1 'sécurité
For Each jour In Array("LUNDI", "MARDI", "MERCREDI", "JEUDI", "VENDREDI", "SAMEDI", "DIMANCHE")
Set deb = [A:A].Find(jour, , xlValues, xlPart)
Set fin = [A:E].Find("Besoin", deb)
h = fin.Row - deb.Row - 1
Range(deb(0, 6), Cells(fin.Row - 1, Columns.Count)).Interior.ColorIndex = xlNone 'RAZ
With Range(deb(0, 6), deb(1, Columns.Count).End(xlToLeft)(0))
.Interior.Color = vbRed 'RAZ en rouge
.ClearContents 'RAZ des valeurs
.Cells(2, 2).Resize(, .Columns.Count - 1) = "=RC[-1]+1/48" 'sécurité, reconstitue les formules des heures
End With
If h > 0 Then
'---couleur bleue---
For Each c In Union(deb(2, 2).Resize(h), deb(2, 4).Resize(h))
i = Application.Match(c + "1E-9", deb.EntireRow)
j = Application.Match(c(1, 2) + "1E-9", deb.EntireRow)
If IsNumeric(i) And IsNumeric(j) Then If j > i Then _
Cells(c.Row, i).Resize(, j - i).Interior.Color = RGB(0, 176, 240)
Next c
'---couleur verte---
For Each c In Range(deb(0, 6), deb(1, Columns.Count).End(xlToLeft)(0))
Nhotesse = Int(Val(Cells(fin.Row, c.Column).MergeArea(1))) 'on se sert des cellules fusionnées...
If Nhotesse > 0 Then
i = 0
For Each cc In c(3).Resize(h)
If cc.Interior.ColorIndex <> xlNone Then i = i + 1
If i >= Nhotesse Then c.Interior.Color = vbGreen
Next cc
If i Then c = i 'FACULTATIF, inscrit le nombre dans la cellule
End If
Next c
End If
Next jour
1 Application.EnableEvents = True 'réactive les évènements
If Err Then MsgBox "Vous savez sûrement ce qui ne va pas !", 48, "Erreur"
End Sub