Function to return "too low", "too high" or "OK" for each cell in a range

Question

I want a function to run through a range of cells and if:

• any are greater than NormalValue then return 'too low',

• NormalValue is greater than double the maximum value in the range then return 'too high',

• neither of these are true, then return 'OK'.

This is what I have come up with so far:

Function TooHighLow(rng As range, NormalValue As Double)

  For Each cell In rng
     If Application.WorksheetFunction.Max(cell.Value) > NormalValue Then
        TooHighLow = "Too Low"

     ElseIf NormalValue > 2 * (Application.WorksheetFunction.Max(cell.Value)) Then
        TooHighLow = "Too High"

     Else
        TooHighLow = "OK"

     End If
  Next cell
End Function 

Answer 1

I think you want something like this:

Function TooHighLow(rng As Range, NormalValue As Double)
    Dim m As Double
    m = Application.WorksheetFunction.Max(rng)
    If m > NormalValue Then
        TooHighLow = "Too Low"
    ElseIf NormalValue > 2 * m Then
        TooHighLow = "Too High"
     Else
        TooHighLow = "OK"
     End If
End Function

1) The loop was pointless

2) You should only compute the max once, storing the result in a variable.