Private Sub Worksheet_Change(ByVal Target As Range)
If Range("K4") = "" Then GoTo deux:
Trois:
Dim liste, i&, j%, P As Range, c As Range, k, cc As Range
Set Target = Intersect(Target, Union(Columns("B"), Columns("D"), Columns("F")), UsedRange)
If Target Is Nothing Then Exit Sub
Application.EnableEvents = False 'désactive les évènements
For Each Target In Target 'si entrées multiples
liste = Range("K2:K4")
i = Target.Row
j = Target.Column
Set P = Union(Cells(i, "B"), Cells(i, "D"), Cells(i, "F"))
Select Case Application.CountA(P)
Case 2
For Each c In P
For k = 1 To 3
If liste(k, 1) = c Then liste(k, 1) = "": Exit For
Next k
If c.Column <> j And c = Target Then Target = "": GoTo 1 'si doublon
Next c
For Each c In P
If c = "" Then
For k = 1 To 3
If liste(k, 1) <> "" Then c = liste(k, 1): GoTo 1 'valeur restante
Next k
End If
Next c
Case 3
For Each cc In P
If cc.Column <> j And cc = Target Then
For Each c In P
For k = 1 To 3
If liste(k, 1) = c Then liste(k, 1) = "": Exit For
Next k, c
For k = 1 To 3
If liste(k, 1) <> "" Then cc = liste(k, 1): GoTo 1 'valeur restante
Next k
End If
Next cc
End Select
1 Next Target
Application.EnableEvents = True 'réactive les évènements
deux:
Dim un As Range, deux As Range, form1, xrg As Range, x, n&, s(0 To 1), v
On Error GoTo FIN:
If Target.Count > 1 Then Exit Sub
If Target = "" Then Exit Sub
Set un = Target
If un.Column = Range("b2").Column Then
Set deux = Cells(Target.Row, "d")
ElseIf un.Column = Range("d2").Column Then
Set deux = Cells(Target.Row, "b")
ElseIf un.Column = Range("c2").Column Then
Set deux = Cells(Target.Row, "e")
ElseIf un.Column = Range("e2").Column Then
Set deux = Cells(Target.Row, "c")
End If
form1 = Target.Validation.Formula1
If Left(form1, 1) = "=" Then
Set xrg = Range(Mid(form1, 2))
If xrg.Count <> 2 Then Exit Sub
For Each x In xrg.Value: s(n) = x: n = n + 1: Next
Else
v = Split(form1, Application.International(xlListSeparator))
If UBound(v) - LBound(v) + 1 <> 2 Then Exit Sub
s(0) = v(0): s(1) = v(1)
End If
Application.EnableEvents = False: deux.Value = IIf(un.Value = s(0), s(1), s(0))
FIN:
Application.EnableEvents = True
End Sub
