I wonder if someone can help me out, I am looping through a range o
cells using the code below. My question is, when the loop identifies
particular cell in the range that doesnt meet the criteria set out i
my code, how do I go about identifying the cell and for example shadin
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, pleas
enter a correct value:"
myString = InputBox("Enter the correct value")
End If

Next myString
MsgBox "finished!"
Unload Me

End Su

--
benjammin
-----------------------------------------------------------------------
benjammind's Profile: http://www.excelforum.com/member.php...fo&userid=1821
View this thread: http://www.excelforum.com/showthread.php?threadid=55641

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

myString.Interior.Colorindex = 15 'this is light grey

Regards,
Stefi

€žbenjammind€� ezt Ã*rta:


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

Thanks for the responses, one thing I forgot to add, once I have
identified, and shaded, the particular cell I want excel to jump/scroll
down to that particular cell.

For example if cells 1 - 49 are displayed on the screen and the code
highlights cell A189 I want excel to go to this location
automatically.

Again, your help is appreciated.
Ben


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

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.Select
myString = InputBox("Enter the correct value")
End If

Next myString
MsgBox "finished!"
Unload Me

End Sub


--
HTH

Bob Phillips

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

"benjammind" wrote
in message ...

Thanks for the responses, one thing I forgot to add, once I have
identified, and shaded, the particular cell I want excel to jump/scroll
down to that particular cell.

For example if cells 1 - 49 are displayed on the screen and the code
highlights cell A189 I want excel to go to this location
automatically.

Again, your help is appreciated.
Ben


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

myString.Interior.Colorindex = 15 'this is light grey
myString.Select

Stefi


€žbenjammind€� ezt Ã*rta:


Thanks for the responses, one thing I forgot to add, once I have
identified, and shaded, the particular cell I want excel to jump/scroll
down to that particular cell.

For example if cells 1 - 49 are displayed on the screen and the code
highlights cell A189 I want excel to go to this location
automatically.

Again, your help is appreciated.
Ben


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