Bob Phillips wrote:
Andrew,
You don't show your code but I knocked up this little function which
is similar to what you describe

Function IsValid(rng As Range)
If rng.Count 1 Then
IsValid = CVErr(xlValue)
Else
IsValid = (rng.Value = 0 And rng.Value <= 100 And Not
IsEmpty(rng.Value))
End If

End Function

If you put =IsValid(B3) in A3 it reruns TRUE or FALSE depending upon
the value.

How does it compare to yours?


My function is:

Function IsValid(Num As Variant) As Boolean
IsValid = (Num = 0 And Num <= 100)
End Function
(the final version will check for various text values as alternatives to
0..100, hence use of Variant)

I have no problem using the function in a formula in a cell (eg.
=IsValid(B3) in cell A3). My problem occurs when I attempt to use the same
formula for Data Validation in as cell. eg in Cell B3 I select:

Data | Validation | Settings | Allow Custom | Formula =IsValid(B3)

This ought(?) to do the same as the worksheet function:

Data | Validation | Settings | Allow Custom | Formula =AND(B3=0,
B3<=100)