Hi somethinglikeant;
Excellent approach!... There's a tiny problem, however, associated with the
Exit Sub in the Error Handling section of your code.
In situations where the error custom message is displayed and acknowledged
by pressing OK, the computation continues and does not wait for the
referenced value in the relevant/focused cell to be fixed first. As a
result, numerous compiler and run-time strange errors appear!!
You might have noticed from my initial post that I commented in each IF:
'don't leave this IF block until the referenced wrong value in C? is fixed.

Regards.


"somethinglikeant" wrote:

Many Thanks

i've been able to learn something here. code may be a little sloppy
(could be shortened i guess) but here goes

Sub CheckValues()
Dim res As Variant:
For i = 1 To 3
If i = 1 Then
ary = Range("A1:A5"): checkcell = [C9]
End If
If i = 2 Then
ary = Range("B1:B6"): checkcell = [C10]
End If
If i = 3 Then
ary = Range("C1:C7"): checkcell = [C11]
End If
res = Application.Match(checkcell, ary, 0)
If IsError(res) Then
MsgBox "You must change value of " & checkcell
Exit Sub
End If
Next i
End Sub

please let me know what you think, and if anyone else has improvements
on this please feel free to post them, it's always good to improve

somethinglikeant






monir wrote:
Hello;

I would like to check the values in 3 cells, each against a separate range
of values, before proceeding with the computation. (And not using Data
Validation Lists).

Suppose the 3 cells to be checked, in the order presented, a
... C9, C10, and C11
and their validation ranges a
... A1:A5, B1:B6, and C1:C7 respectively.

I would appreciate your help to include in the VBA code something like:
IF Range("C9").Value < 'any value in the Range("A1:A5") Then
MsgBox "You must Change C9 value"
' don't exit this IF until you fix the value in C9
Enf IF
IF Range("C10").Value < 'any value in Range("B1:B6") Then
MsgBox "You must Change C10 value"
' don't exit this IF until you fix the value in C10
Enf IF
IF Range("C11").Value < 'any value in Range("C1:C7") Then
MsgBox "You must Change C11 value"
' don't exit this IF until you fix the value in C11
Enf IF
'continue with the program

Thank you.