Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, P As Range, vide, n, a(), col
Set r = Intersect(Target, Range("E3:G" & Rows.Count), Me.UsedRange.EntireRow)
If r Is Nothing Then Exit Sub
For Each r In r 'si entrées multiples(copier-coller)
If CStr(r) <> "" And Not IsNumeric(r.Value2) Then Application.Undo: End
Set P = Intersect([E:G], r.EntireRow)
vide = Application.CountBlank(P) 'nombre de cellules vides
If vide = 0 Then
If P(2) <> P(1) + P(3) Then Application.Undo: End 'annulation
ElseIf vide = 1 Then
n = n + 1
ReDim Preserve a(1 To 2, 1 To n)
For col = 1 To 3
If P(col) = "" Then Exit For
Next col
a(1, n) = P(col).Address
Select Case col
Case 1: a(2, n) = P(2) - P(3)
Case 2: a(2, n) = P(1) + P(3)
Case 3: a(2, n) = P(2) - P(1)
End Select
End If
Next r
For col = 1 To n
Range(a(1, col)) = a(2, col)
Next col
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, P As Range, col, vide, n, a()
Set r = Intersect(Target, Range("E3:G" & Rows.Count), Me.UsedRange.EntireRow)
If r Is Nothing Then Exit Sub
For Each r In r 'si entrées multiples(copier-coller)
Set P = Intersect([E:G], r.EntireRow)
For col = 1 To 3
If Not IsNumeric(P(col).Value2) Then Application.Undo: End 'annulation
Next col
vide = Application.CountBlank(P) 'nombre de cellules vides
If vide = 0 Then
If P(2).Value2 <> P(1).Value2 + P(3).Value2 Then Application.Undo: End 'annulation
ElseIf vide = 1 Then
n = n + 1
ReDim Preserve a(1 To 2, 1 To n)
For col = 1 To 3
If P(col).Value2 = "" Then Exit For
Next col
a(1, n) = P(col).Address
Select Case col
Case 1: a(2, n) = P(2).Value2 - P(3).Value2
Case 2: a(2, n) = P(1).Value2 + P(3).Value2
Case 3: a(2, n) = P(2).Value2 - P(1).Value2
End Select
End If
Next r
For col = 1 To n
Range(a(1, col)) = a(2, col)
Next col
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("E3:G" & Rows.Count), Target) Is Nothing Then
Application.EnableEvents = False
Select Case Target.Column
Case 5: If Target.Value = "" Then Range(Target.Offset(, 1), Target.Offset(, 2)).ClearContents
Case 6: Target.Offset(, 1) = IIf(Target <> "", Target - Target.Offset(, -1), "")
Case 7: Target.Offset(, -1) = IIf(Target <> "", Target + Target.Offset(, -2), "")
End Select
Application.EnableEvents = True
End If
End Sub
Option Explicit
Dim Cel As Range
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("E3:G" & Rows.Count), Target) Is Nothing And Selection.Columns.Count = 1 Then
Application.EnableEvents = False
For Each Cel In Target
Select Case Target.Column
Case 5: If Cel.Value = "" Then Range(Cel.Offset(, 1), Cel.Offset(, 2)).ClearContents
Case 6: If Not Cel.Offset(, -1) = "" Then Cel.Offset(, 1) = IIf(Cel <> "", Cel - Cel.Offset(, -1), "")
Case 7
If Not Cel.Offset(, -2) = "" Then Cel.Offset(, -1) = IIf(Cel <> "", Cel + Cel.Offset(, -2), "") Else Cel = ""
End Select
Next
Application.EnableEvents = True
End If
End Sub