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
| Sub Tester_Box_Archives()
Dim Plage_MFC As Excel.Range, FC1 As Excel.FormatCondition, FC2 As Excel.FormatCondition
Dim c As Long
Dim Col_Date As String, Col_Box As String
Application.ScreenUpdating = False
On Error Resume Next
DerCol = Range("XFD6").End(xlToLeft).Column
DerLig = Range("B" & Rows.Count).End(xlUp).Row
For c = 5 To DerCol
If Cells(6, c) = "Box " & Chr(10) & "Archives" Then
Set Plage_MFC = Range(Cells(7, c - 2), Cells(DerLig, c))
Plage_MFC.FormatConditions.Delete
'Les conditions
Col_Date = Split(Cells(7, c - 1).Address, "$")(1) 'Colonne Date Archives
Col_Box = Split(Cells(7, c).Address, "$")(1) 'Colonne Box Archives
Range(Col_Box & 7).Select 'on se positionne sur la première cellule de la plage ou s'applique la MFC
'Formule "En Retard"
Formule1 = "=ET($" & Col_Date & "7<>"""";$" & Col_Date & "7<$D$5;$" & Col_Box & "7="""")"
Set FC1 = Plage_MFC.FormatConditions.Add(Type:=xlExpression, Formula1:=Formule1)
FC1.Interior.Color = RGB(255, 0, 0)
FC1.Font.Color = RGB(255, 255, 0)
'Formule "Ok"
Formule2 = "=$" & Col_Box & "7=""OK"""
Set FC2 = Plage_MFC.FormatConditions.Add(Type:=xlExpression, Formula1:=Formule2)
FC2.Interior.Color = RGB(199, 239, 206)
FC2.Font.Color = RGB(0, 0, 0)
End If
Next c
'Libération de la mémoire
Set Plage_MFC = Nothing
Set FC1 = Nothing
Set FC2 = Nothing
End Sub |
Partager