Sub Qui_combien()
' variable déclarer
Dim c As Range
' temps d'exécution accélérer
With Application: .ScreenUpdating = False: .Calculation = xlManual: .EnableEvents = False: End With
'colonne 15 effacer, colonne 1 insérer
Columns(15).Clear: Columns(1).Insert
' constantes des colonnes b à d
For Each c In Columns("b:b").SpecialCells(xlCellTypeConstants, 2)
' si couleur = jaune, copier en colonne a
If c.Interior.ColorIndex = 6 Then c.Copy Destination:=Range("a" & Rows.Count).End(xlUp)(2)
Next
' formule en colonne 12 insérer
With Range("P2:P" & Cells(Rows.Count, 15).End(xlUp).Row)
' =NB.SI(B:D;J1)
.FormulaR1C1 = "=COUNTIF(C[-15],RC[-1])"
' formule convertir en valeur
.Value = .Value
End With
' colonne 1 supprimer
Columns(1).Delete
With Application: .EnableEvents = True: .Calculation = xlAutomatic: .ScreenUpdating = True: End With
End Sub