1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29
| Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lo As ListObject, r As Range
Dim kR As Long, kC As Long
Dim kC1 As Long, kC2 As Long, kC3 As Long
If Target.Count > 1 Then Exit Sub
Set lo = ActiveSheet.ListObjects("Tableau1")
Set r = lo.HeaderRowRange
For kC = 1 To r.Columns.Count
If r(kC) = "Note1" Then kC1 = r(kC).Column
If r(kC) = "Note2" Then kC2 = r(kC).Column
If r(kC) = "Note3" Then kC3 = r(kC).Column
Next kC
Set r = Intersect(Target, lo.DataBodyRange)
If r Is Nothing Then
'--- hors tableau, ne rien faire
Else
kR = Target.Row
kC = Target.Column
If kC = kC1 Or kC = kC2 Then
If Cells(kR, kC1) <> "" And Cells(kR, kC2) <> "" Then
If Abs(Cells(kR, kC1) - Cells(kR, kC2)) <= 3 Then
Cells(kR, kC3) = (Cells(kR, kC1) + Cells(kR, kC2)) / 2
End If
End If
End If
End If
End Sub |
Partager