Option Explicit
Function speed(r As Range) As String
Dim a, addr, colorx
Dim i As Long, nr As Long, srow As Long, scol As Long, maxspeed As Long, nc As Long
Dim Pickup As Long, sline As Long, Elbow As Long, tSpeed As Long
colorx = Array("Grey", "Orange")
a = Range("Trim_Speed")
Pickup = r(1, 1): sline = r(2, 1): Elbow = r(3, 1): tSpeed = r(4, 1)
' find column
scol = Application.Match(sline, Range("Suction_Line"), 1)
' find row
srow = Application.Match(Elbow, Range("elbows"), 0) _
+ Application.Match(Pickup, Range("pickup_Ø"), 0) - 1
If Pickup = 100 Then
nr = 1: nc = 1
Else
nr = 2: nc = 0
End If
For i = 1 To nr
If tSpeed <= a(srow, scol) Then
speed = colorx(nc)
' addr = Cells(srow + 4, scol + 3).Address
Exit Function
End If
srow = srow + 1: nc = nc + 1
Next i
speed = "N/A"
End Function
in N10
=speed($N$5:$N$8)
Bookmarks