Loop help

[benjammind]

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

 

[Bob Phillips]

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)

 

[Stefi]

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

Regards,
Stefi

„benjammind†ezt írta:

 

[benjammind]

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

 

[Stefi]

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

Stefi


„benjammind†ezt írta:

 

[Bob Phillips]

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)