Dim myString As Range
Dim TrimString As String

For Each myString In Range("B2:B10")
TrimString = Trim(myString)

If Len(TrimString) = 16 Or Len(TrimString) = 10 Then
Else

MsgBox "Cell: " & myString.Address & ", Value: " &myString & _
"incorrect, doesnt equal 16 or 10, please enter a correct
value:"
myString = InputBox("Enter the correct value")
End If

Next myString
MsgBox "finished!"
Unload Me

End Sub

I would use conditional formatting to highlight cells out of range.



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"benjammind" wrote
in message ...

I wonder if someone can help me out, I am looping through a range of
cells using the code below. My question is, when the loop identifies a
particular cell in the range that doesnt meet the criteria set out in
my code, how do I go about identifying the cell and for example shading
the cell and displaying a messagebox to the user?

Any help is much appreciated!
Ben


----------------------------------------------------------------
Dim myString As Range
Dim TrimString As String

For Each myString In Range("B2:B10")
TrimString = Trim(myString)

If Len(TrimString) = 16 Or Len(TrimString) = 10 Then
Else

MsgBox myString & "incorrect, doesnt equal 16 or 10, please
enter a correct value:"
myString = InputBox("Enter the correct value")
End If

Next myString
MsgBox "finished!"
Unload Me

End Sub


--
benjammind
------------------------------------------------------------------------
benjammind's Profile:
http://www.excelforum.com/member.php...o&userid=18217
View this thread: http://www.excelforum.com/showthread...hreadid=556417