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
| Sub test()
'Set Var = ActiveCell.FormatConditions
Dim C As Range, Ctr As Byte, Jours As Integer, tot As Integer
With Sheets("Feuil1")
For Each C In .Range(.[A2], .Cells(.Rows.Count, 1).End(xlUp)).Offset(, 14)
Ctr = 0
For i = 2 To 13
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
Ctr = Ctr + 1
Exit For
End If
End If
If InStr(1, Var, "INAPTE") > 0 Then
If .Cells(C.Row, i) = "INAPTE" Then
Ctr = Ctr + 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
Ctr = Ctr + 1
Exit For
End If
End If
Next x
Next i
If Ctr = 12 Then
C.Interior.ColorIndex = 3
tot = tot + 1
Else
C.Interior.ColorIndex = xlNone
End If
Next C
.Cells(.Cells(.Rows.Count, 1).End(xlUp).Row + 1, 15) = tot
End With
End Sub |
Partager