Private Sub Worksheet_Change(ByVal Target As Range)
Dim h&, P1 As Range, P2 As Range, c As Range, t$, n%, i%
h = UsedRange.Rows.Count + UsedRange.Row - 5
If h < 1 Then Exit Sub
Application.ScreenUpdating = False
Application.EnableEvents = False 'désactive les évènements
Set P1 = [B2,D2,F2,H2,J2] 'adapter éventuellement
For Each c In P1
c = "=Tot(" & c(4).Resize(h).Address & ")"
c = c 'supprime la formule
Next
Set P2 = [J5].Resize(h) 'adapter éventuellement
P2 = "=REPT(""'""&Tot((RC[-8],RC[-6],RC[-4],RC[-2])),Tot((RC[-8],RC[-6],RC[-4],RC[-2]))<>"""")"
P2 = P2.Value 'supprime les formules
'---mise en couleurs---
For Each c In Union(P1, P2)
t = c
If t <> "" Then
c.Font.ColorIndex = xlAutomatic 'RAZ
n = 1
For i = 1 To Len(t)
If Mid(t, i, 1) = "/" Then
n = n + 1
Else
c.Characters(i, 1).Font.ColorIndex = IIf(n = 1, 3, IIf(n = 2, 5, 4)) 'rouge, bleu, vert
End If
Next i
End If
Next c
Application.EnableEvents = True 'réactive les évènements
End Sub