faire appel à la mise en forme conditionnelle vba excel
Sub testMFC()
Dim i As Long, dl As Long
Dim plage As Range
Application.ScreenUpdating = False
With Sheets("Suivi des commandes")
dl = .Range("A" & Rows.Count).End(xlUp).Row
Set plage = .Range("J3:J" & dl)
With plage
.FormatConditions.Delete
.FormatConditions.Add(xlExpression, , "=ET($I3="""";$J3="""")").Interior _
.Color = RGB(224, 224, 224) 'CAS 1 : GRIS
.FormatConditions.Add(xlExpression, , "=ET(Réglages!$D$4<$J3;$K3="""")").Interior _
.Color = RGB(0, 255, 0) 'CAS 2 : VERT
.FormatConditions.Add(xlExpression, , "=ET(Réglages!$D$4>$J3;$K3=""OUI"")").Interior _
.Color = RGB(0, 255, 0) 'CAS 3 : VERT
.FormatConditions.Add(xlExpression, , "=ET(Réglages!$D$4>$J3;$K3=""NON"")").Interior _
.Color = RGB(255, 0, 0) 'CAS 4 : ROUGE
.FormatConditions.Add(xlExpression, , "=ET(Réglages!$D$4>$J3;$K3="""")").Interior _
.Color = RGB(224, 224, 224) 'CAS autre : Gris (si < et rien)
.FormatConditions.Add(xlExpression, , "=ET(Réglages!$D$4<$J3;$K3=""NON"")").Interior _
.Color = RGB(255, 0, 0) 'CAS autre : ROUGE (si > et NON)
End With
End With
Application.ScreenUpdating = True
End Sub