Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, [B:F]) Is Nothing Then Exit Sub
Dim vide As Boolean, fournisseur$, critere$, tablo, resu(), i&, n&
vide = [B2] & [C2] & [D2] & [E2] = ""
fournisseur = [B2]
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
Next
'---restitution---
Application.EnableEvents = False 'd_sactive les _vnementssss
If FilterMode Then ShowAllData 'si la feuille est filtr_e
With [B5] '1re cellule de restitutionnnn
If n Then
.Resize(n, 7) = resu
.Resize(n, 7).Borders.Weight = xlThin 'bordures
End If
.Offset(n).Resize(Rows.Count - n - .Row + 1, 7).ClearContents 'RAZ en dessous
.Offset(n).Resize(Rows.Count - n - .Row + 1, 7).Borders.LineStyle = xlNone
End With
Columns(3).AutoFit 'ajustement largeur
ActiveWindow.ScrollRow = 1 'cadrage
With UsedRange: End With 'actualise la barre de d_filement verticale
Application.EnableEvents = True 'r_active les _vnementssss
End Sub