Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, [B:F]) Is Nothing Then Exit Sub
Dim vide As Boolean, fournisseur$, couleur$, critere$, tablo, resu(), i&, n&
vide = [B2] & [C2] & [D2] & [E2] = ""
fournisseur = [B2]
couleur = [D2]
critere = LCase(fournisseur & Chr(1) & CStr([C2])) & "*" 'textes commenant par C2....
tablo = Sheets("BDD_Technique").[A2].CurrentRegion.Resize(, 6) 'matrice, plus rapide
ReDim resu(1 To UBound(tablo), 1 To 7)
For i = 2 To UBound(tablo)
If Not vide And LCase(IIf(fournisseur = "", "", tablo(i, 4)) & Chr(1) & tablo(i, 1)) Like critere Then
n = n + 1
resu(n, 1) = tablo(i, 4) 'Numero 4 = Fournisseur / resu(n, 1) = B
resu(n, 2) = tablo(i, 1) 'Numero 1 = plante /resu(n, 2) = C
resu(n, 3) = tablo(i, 2) 'Numero 2 = couleur/resu(n, 3) = D
resu(n, 4) = tablo(i, 5) 'Numéro 5 = contenance / resu(n, 4) =E
resu(n, 5) = tablo(i, 6) 'Numero 6 = Marché / resu(n, 5) =F
End If