Hi OTPM,
Add the following function to a code module attached to your workbook:
Code:
Option Explicit
Function RiskTest(Rng1 As Range, Rng2 As Range, Rng3 As Range) As String
Dim i As Long, j As Long
RiskTest = "On Time"
With Rng1
For i = .Column To .EntireRow.End(xlToRight).Column
For j = 1 To .Row - 1
If .Offset(j - .Row, 1 - .Column).Value = .Offset(0, i - .Column).Value Then
If .Offset(j - .Row, Rng3.Column - .Column).Value < Rng2.Value Then
RiskTest = "At Risk"
Exit Function
End If
End If
Next
Next
End With
End Function
You can then use this function like a formula. It takes three parameters:
• the address of the first predecessor to test
• the address of the cell holding the completion date against which to test the predecessors; and
• the column holding the predecessor completion dates.
So, if you want to test the the set of predecessors starting in I11 against a completion date in F13 and the predecessor completion dates are in column F, you'd used the formula:
=RiskTest(I11,F$13,F:F)