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
| Sub test()
Dim C As Range, CtrRouge As Byte, CtrBleu As Byte, Jours As Integer, TotRouge As Integer, TotBleu As Integer
With Sheets("Feuil1")
For Each C In .Range(.[a2], .Cells(.Rows.Count, 1).End(xlUp)).Offset(, 14)
CtrRouge = 0
CtrBleu = 0
For i = 2 To 14
For x = 1 To .Cells(C.Row, i).FormatConditions.Count
Set Var = .Cells(C.Row, i).FormatConditions
Var = .Cells(C.Row, i).FormatConditions(x).Formula1
If Left(Var, 8) = "=ESTVIDE" Then
If .Cells(C.Row, i) = "" Then
CtrRouge = CtrRouge + 1
Exit For
End If
End If
If InStr(1, Var, "INAPTE") > 0 Then
If .Cells(C.Row, i) = "INAPTE" Then
CtrBleu = CtrBleu + 1
Exit For
End If
End If
If InStr(1, Var, "AUJOURDHUI") > 0 Then
Jours = CInt(Split(Var, "-")(1))
If .Cells(C.Row, i) < Date - Jours Then
CtrRouge = CtrRouge + 1
Exit For
End If
End If
Next x
Next i
If CtrRouge + CtrBleu > 0 Then
C.Interior.ColorIndex = 3
TotRouge = TotRouge + 1
ElseIf CtrBleu = 13 Then
C.Interior.ColorIndex = 25
TotBleu = TotBleu + 1
Else
C.Interior.ColorIndex = -4142
End If
Next C
[Feuil2!A2] = TotRouge
[Feuil2!B2] = TotBleu
End With
End Sub |
Partager