' Feuil1
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("b:b,n:n,r:r,t:t,x:x,z:z")) Is Nothing Then majVehicule
End Sub
' Module1
Sub majVehicule()
Dim datas, listeVe, ve As String
Dim dictVe, lig As Long, i As Long
Set dictVe = CreateObject("Scripting.Dictionary")
' liste véhicules
listeVe = [VEHICULE].Value
For i = 1 To UBound(listeVe)
If listeVe(i, 1) <> "" Then dictVe(listeVe(i, 1)) = 1
Next i
'données aller-retour
With Worksheets("Feuil1")
datas = .[A3:Z3].Resize(.Cells(Rows.Count, 2).End(xlUp).Row - 2).Value
End With
' liste véhicules restants
For lig = 1 To UBound(datas)
If datas(lig, 2) <> "" And datas(lig, 14) <> "" And datas(lig, 18) = "" Then _
If dictVe.Exists(datas(lig, 14)) Then dictVe.Remove datas(lig, 14)
If datas(lig, 20) <> "" And datas(lig, 24) <> "" And datas(lig, 26) = "" Then _
If dictVe.Exists(datas(lig, 24)) Then dictVe.Remove datas(lig, 24)
Next lig
' coller
With Worksheets("Feuil2")
If .Cells(Rows.Count, 2).End(xlUp).Row > 1 Then
.[B2].Resize(.Cells(Rows.Count, 2).End(xlUp).Row - 1).ClearContents
End If
.[B2].Resize(dictVe.Count) = Application.Transpose(dictVe.keys)
End With
End Sub