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 30 31 32 33 34 35 36 37
| Dim DerLig As Long, DerCol As Long, L As Long, C As Long, I As Long, Col As Long
Dim Valeur As String, Nom As String
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Sortie
Application.ScreenUpdating = False
Application.EnableEvents = False
DerCol = Range("A1").End(xlToRight).Column
For C = 1 To DerCol - 1 Step 2
DerLig = Cells(1, C).End(xlDown).Row
For L = 2 To DerLig
Nom = Cells(L, C)
Detect
Cells(L, C + 1) = Valeur
Next L
Next C
Sortie:
Application.EnableEvents = True
End Sub
Sub Detect()
Dim DerCol As Long, I As Long, DerLig As Long, Nb As Long
DerCol = Range("A1").End(xlToRight).Column
For I = 2 To DerCol Step 2
Valeur = "NON"
If C <> I - 1 Then
DerLig = Cells(1, I - 1).End(xlDown).Row
On Error Resume Next
Nb = Application.WorksheetFunction.Match(Nom, Range(Cells(2, I - 1), Cells(DerLig, I - 1)), 0)
If Err.Number = 0 Then
Valeur = "OUI"
Exit For
End If
On Error GoTo 0
End If
Next I
End Sub |
Partager