Double clic pour mettre mots et formules...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim r As Range, a, i As Variant
Set r = [B10:B30] 'à adapter
If Intersect(r, Target) Is Nothing Then Exit Sub
Cancel = True
a = Array("Total prestation(s)", "Total matériaux", "")
i = Application.Match(Target, a, 0)
If IsError(i) Then i = 0
If i = 3 Then i = 0
Target = a(i)
Target(1, 2) = IIf(i = 2, "", "=SUM(" & r(0, 2).Address(1, 0) & ":OFFSET(" & Target(1, 2).Address(0, 0) & ",-1,))")
End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim r As Range, a, i As Variant, f1$, f2$
Set r = [B10:B30] 'à adapter
If Intersect(r, Target) Is Nothing Then Exit Sub
Cancel = True
a = Array("Total prestation(s)", "Total matériaux", "")
i = Application.Match(Target, a, 0)
If IsError(i) Then i = 0
If i = 3 Then i = 0
Target = a(i)
f1 = r(0, 2).Address(1, 0) & ":OFFSET(" & Target(1, 2).Address(0, 0) & ",-1,)"
f2 = r(0).Address(1, 0) & ":OFFSET(" & Target.Address(0, 0) & ",-1,)"
Target(1, 2) = IIf(i = 2, "", "=SUM(" & f1 & ") -2*SUMIF(" & f2 & ",""Total*""," & f1 & ")")
End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim r As Range, a, i As Variant, f1$, f2$
Set r = [B10:B30,B35:B55] 'à adapter, 2 Areas
If Intersect(r, Target) Is Nothing Then Exit Sub
Set r = r.Areas(IIf(Intersect(Target, r.Areas(2)) Is Nothing, 1, 2)) 'redéfinition de la zone
Cancel = True
a = Array("Total prestation(s)", "Total matériaux", "")
i = Application.Match(Target, a, 0)
If IsError(i) Then i = 0
If i = 3 Then i = 0
Target = a(i)
f1 = r(0, 2).Address(1, 0) & ":OFFSET(" & Target(1, 2).Address(0, 0) & ",-1,)"
f2 = r(0).Address(1, 0) & ":OFFSET(" & Target.Address(0, 0) & ",-1,)"
Target(1, 2) = IIf(i = 2, "", "=SUM(" & f1 & ") -2*SUMIF(" & f2 & ",""Total*""," & f1 & ")")
End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim r As Range, a, i As Variant, f1$, f2$
Set r = [Zone1,Zone2] 'à adapter, 2 Areas nommées
If Intersect(r, Target) Is Nothing Then Exit Sub
Set r = r.Areas(IIf(Intersect(Target, r.Areas(2)) Is Nothing, 1, 2))
Cancel = True
a = Array("Total prestation(s)", "Total matériaux", "")
i = Application.Match(Target, a, 0)
If IsError(i) Then i = 0
If i = 3 Then i = 0
Target = a(i)
f1 = r(0, 2).Address(1, 0) & ":OFFSET(" & Target(1, 2).Address(0, 0) & ",-1,)"
f2 = r(0).Address(1, 0) & ":OFFSET(" & Target.Address(0, 0) & ",-1,)"
Target(1, 2) = IIf(i = 2, "", "=SUM(" & f1 & ") -2*SUMIF(" & f2 & ",""Total*""," & f1 & ")")
End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim col%, deb$, fin$, i As Variant, r As Range, a, f1$, f2$
col = 2 'colonne B
deb = "DÉTAIL": fin = "TOTAL T.T.C" 'titres à rechercher
If Target.Column <> col Or Target.Row = 1 Then Exit Sub
If Target = deb Or Application.CountIf(Cells(1, col).Resize(Target.Row - 1), deb) = 0 Then Exit Sub
If Target = fin Or Application.CountIf(Cells(Target.Row + 1, col).Resize(Rows.Count - Target.Row), fin) = 0 Then Exit Sub
For i = Target.Row - 1 To 1 Step -1
    If Cells(i, col) = fin Then Exit Sub
    If Cells(i, col) = deb Then Set r = Cells(i, col): Exit For
Next
Cancel = True
a = Array("Total prestation(s)", "Total matériaux", "")
i = Application.Match(Target, a, 0)
If IsError(i) Then i = 0
If i = 3 Then i = 0
Target = a(i)
f1 = r(1, 2).Address(1, 0) & ":OFFSET(" & Target(1, 2).Address(0, 0) & ",-1,)"
f2 = r.Address(1, 0) & ":OFFSET(" & Target.Address(0, 0) & ",-1,)"
Target(1, 2) = IIf(i = 2, "", "=SUM(" & f1 & ") -2*SUMIF(" & f2 & ",""Total*""," & f1 & ")")
End Sub

Target.HorizontalAlignment = IIf(i = 2, xlLeft, xlCenter) 'alignement