Option Explicit
Dim bon As Boolean
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Coul_1 As Long
Dim Coul_2 As Long
Dim Coul_3 As Long
Dim Coul_4 As Long
Dim Coul_5 As Long
Dim ligne As Range
Dim totalM As Double
Dim totalO As Double
Dim totalI As Double
Dim couleur As Long
Dim c As Range
Dim dercolonne As Integer
Dim derligne As Integer
Dim plage As Range
Dim colonnerenvoi As Integer
If bon = True Then
bon = False
Exit Sub
Else
If Target.Row = 9 Then
bon = True
Target = UCase(Target(1))
Select Case Target(1)
Case "M"
Coul_1 = 37 'titre
Coul_2 = 5 ' G
Coul_3 = 33 ' D
Coul_4 = 37 ' colG
Coul_5 = 34 'col D
Case "O"
Coul_1 = 4 'titre
Coul_2 = 50 ' G
Coul_3 = 35 ' D
Coul_4 = 35 ' colG
Coul_5 = 4 'col D
Case "I"
Coul_1 = 3 'titre
Coul_2 = 3 ' G
Coul_3 = 38 ' D
Coul_4 = 38 ' colG
Coul_5 = 3 'col D
Case Else
Coul_1 = -4142 'titre
Coul_2 = -4142 ' G
Coul_3 = -4142 ' D
Coul_4 = -4142 ' colG
Coul_5 = -4142 'col D
End Select
'Titre
Cells(Target.Row - 3, Target.Column).Interior.ColorIndex = Coul_1
Cells(Target.Row + 1, Target.Column).Interior.ColorIndex = Coul_2
Cells(Target.Row + 1, Target.Column + 2).Interior.ColorIndex = Coul_3
Range(Cells(11, Target.Column).Address & ":" & _
Cells(62, Target.Column).Address).Interior.ColorIndex = Coul_4
Range(Cells(11, Target.Column + 2).Address & ":" & _
Cells(62, Target.Column + 2).Address).Interior.ColorIndex = Coul_5
If Coul_5 = -4142 Then MsgBox ("Remise à blanc!")
End If
End If
dercolonne = Range("iv10").End(xlToLeft).Column
derligne = Range("a65536").End(xlUp).Row
colonnerenvoi = Range("iv6").End(xlToLeft).Column
Set plage = Range("c11", Cells(derligne, dercolonne))
'MsgBox plage.Address
If Not Intersect(Target, plage) Is Nothing Then
Set ligne = Range(Cells(Target.Row, "C"), Cells(Target.Row, dercolonne))
For Each c In ligne
couleur = c.Interior.ColorIndex
If couleur = 37 Or couleur = 35 Or couleur = 38 Then
Select Case Cells(9, c.Column)
Case "M": totalM = totalM + c
Case "I": totalI = totalI + c
Case "O": totalO = totalO + c
End Select
End If
Next c
Cells(Target.Row, colonnerenvoi - 2) = totalM
Cells(Target.Row, colonnerenvoi - 1) = totalI
Cells(Target.Row, colonnerenvoi) = totalO
End If
End Sub