Sub MFC()
Dim Statut As Range
Dim i&, d&, v()
Application.DisplayAlerts = False
Names.Add "DATE_REF", [A1]
With [K9]
With Range(.Cells, .End(xlDown))
Names.Add "Nb_jours_restants", .Cells
Names.Add "STATUT", .Offset(, -7).Resize(.Rows.Count, 2)
End With
End With
Application.DisplayAlerts = True
d = Range("DATE_REF").Value
Set Statut = Range("STATUT")
v = Statut.Value
Application.ScreenUpdating = False
With Range("Nb_jours_restants")
.Cells.Interior.ColorIndex = xlColorIndexNone
For i = 2 To UBound(v)
With .Cells(i).Interior
If IsDate(v(i, 1)) Then
Select Case v(i, 1)
Case Is > d + 10: v(i, 2) = "J": .Color = vbGreen
Case Is > d + 3: v(i, 2) = "K": .Color = 49407
Case Is >= d: v(i, 2) = "L": .Color = vbRed
Case Is < d: v(i, 2) = "x": .Color = vbRed
End Select
Else
Select Case v(i, 1)
Case "SLV": v(i, 2) = "n": .Color = vbYellow
Case Else: v(i, 2) = ""
End Select
End If
End With
Next
End With
Statut.Value = v
Application.ScreenUpdating = True
End Sub