1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
| Sub mfccomplexe()
Dim i As Integer, nb As Integer
nb = Application.WorksheetFunction.CountA(ActiveWorkbook.Sheets("Rate Sheet").Columns("A")) - 5
If nb = -1 Or nb = 0 Then
nb = nb + 2
End If
For i = 5 To nb + 5
Range("AG" & i).Select
Selection.FormatConditions.Add Type:=xlExpression, _
Formula1:="=ET(OU($AE$" & i & "=""ROUTINE GATEWAY"";$AE$" & i & "=""CRITICAL"";$AE$" & i & "=""AOG"");SOMMEPROD(($O$" & i & ":$O$" & nb & "=$O" & i & ")*($R$" & i & ":$R$" & nb & "=$R" & i & ")*($AE$" & i & ":$AE$" & nb & "=""ROUTINE GATEWAY"")*$AG$" & i & ":$AG$" & nb & ")>SOMMEPROD(($O$" & i & ":$O$" & nb & "=$O" & i & ")*($R$" & i & ":$R$" & nb & "=$R" & i & ")*($AE$" & i & ":$AE$" & nb & "=""CRITICAL"")*$AG$" & i & ":$AG$" & nb & ")>SOMMEPROD(($O$" & i & ":$O$" & nb & "=$O" & i & ")*($R$" & i & ":$R$" & nb & "=$R" & i & ")*($AE$" & i & ":$AE$" & nb & "=""AOG"")*$AG$" & i & ":$AG$" & nb & "))"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.color = 3
.TintAndShade = 0
End With
Next i
End sub |