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 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68
| Voici, remplacez tout le code précédent par celui-ci
Dim DerLig As Long
Sub Delet()
Application.ScreenUpdating = False
Range("A3:J59").ClearContents
Formules
MFC_Vehicules
MFC_Colonne_Sables
End Sub
Sub Formules()
Application.ScreenUpdating = False
DerLig = Range("A" & Rows.Count).End(xlDown).Row
Range("M4").FormulaR1C1 = "=COUNTIF(C1,""T7*"")"
Range("M6").FormulaR1C1 = "=COUNTIF(C1,""T2*"")"
Range("M8").FormulaR1C1 = "=COUNTIF(C1,""T3*"")"
Range("M10").FormulaR1C1 = "=COUNTIF(C1,""T4*"")"
Range("M13").FormulaR1C1 = "=R3C+R[-7]C+R[-5]C+R[-3]C"
Range("M16").FormulaR1C1 = "=COUNTIF(R3C8:R" & DerLig & "C8,""Sable"")"
End Sub
Sub MFC_Vehicules()
Dim DerLig As Long
Formule = "=ET($L$20<>"""";$A3=$L$20)"
DerLig = Range("A" & Rows.Count).End(xlDown).Row
Range("A3").Select
'Efface tous les formats conditionnels existants sur toute la plage sélectionnée
Range("A3:A" & DerLig).FormatConditions.Delete
'Ajoute un Format conditionnel
Range("A3:A" & DerLig).FormatConditions.Add(xlExpression, xlLess, Formule).Interior.Color = RGB(255, 192, 0) 'Orange
End Sub
Sub MFC_Colonne_Sables()
Application.ScreenUpdating = False
On Error Resume Next
'*************************************************************************************
Dim Plage_MFC As Excel.Range, FC1 As Excel.FormatCondition, FC2 As Excel.FormatCondition, FC3 As Excel.FormatCondition, FC4 As Excel.FormatCondition
DerLig = Range("A" & Rows.Count).End(xlDown).Row
Range("H3").Select
Set Plage_MFC = Range("H3:I" & DerLig)
Plage_MFC.FormatConditions.Delete
Set FC1 = Plage_MFC.FormatConditions.Add(Type:=xlExpression, Formula1:="=$H3=""vh s3*""") 'Bleu clair
FC1.Interior.Color = RGB(153, 204, 255)
FC1.Font.Color = RGB(0, 0, 0)
Set FC2 = Plage_MFC.FormatConditions.Add(Type:=xlExpression, Formula1:="=$H3=""Sable""") 'Vert
FC2.Interior.Color = RGB(0, 255, 0)
FC2.Font.Color = RGB(0, 0, 0)
Set FC3 = Plage_MFC.FormatConditions.Add(Type:=xlExpression, Formula1:="=$H3=""Collision""") 'Orange
FC3.Interior.Color = RGB(255, 102, 0)
FC3.Font.Color = RGB(255, 255, 255)
Set FC4 = Plage_MFC.FormatConditions.Add(Type:=xlExpression, Formula1:="=$H3=""Lav s2/s3*""") 'Bleu clair
FC4.Interior.Color = RGB(153, 204, 255)
FC4.Font.Color = RGB(0, 0, 0)
'***** réinitialisation des variables *******************************************
Set FC1 = Nothing
Set FC2 = Nothing
Set FC3 = Nothing
Set FC4 = Nothing
Set Plage_MFC = Nothing
End Sub |