loop and findnext

Can someone tell me why this won't find the next value in the loop... it
stays on the first one and crashes my excel...

heets("c").Select
Set wks = ActiveSheet
Set rngToSearch = wks.Columns(1)
Set RngFound = rngToSearch.Find(what:="new", LookIn:=xlValues,
lookat:=xlWhole)
If RngFound Is Nothing Then
MsgBox "Nothing"
Else
Do
r = RngFound.Row
Range("a" & r, "z" & r).Select
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
Range("a" & r).Select
Set RngFound = rngToSearch.FindNext
Loop Until RngFound Is Nothing
End If
End Sub

sheets("c").Select
Set wks = ActiveSheet
Set rngToSearch = wks.Columns(1)
Set RngFound = rngToSearch.Find(what:="new", LookIn:=xlValues,
lookat:=xlWhole)
If RngFound Is Nothing Then
MsgBox "Nothing"
Else
sAddr = RngFound.Address
Do
r = RngFound.Row
Range("a" & r, "z" & r).Select
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
Range("a" & r).Select
Set RngFound = rngToSearch.FindNext(rngFound)
Loop Until RngFound Is Nothing or RngFound.Address = _
sAddr
End If
End Sub

--
Regards,
Tom Ogilvy

"John" <John@discussions.microsoft.com> wrote in message
news:E2A5624E-6E67-46B8-AACB-CDF640EA4F81@microsoft.com...
> Can someone tell me why this won't find the next value in the loop... it
> stays on the first one and crashes my excel...
>
> heets("c").Select
> Set wks = ActiveSheet
> Set rngToSearch = wks.Columns(1)
> Set RngFound = rngToSearch.Find(what:="new", LookIn:=xlValues,
> lookat:=xlWhole)
> If RngFound Is Nothing Then
> MsgBox "Nothing"
> Else
> Do
> r = RngFound.Row
> Range("a" & r, "z" & r).Select
> With Selection.Interior
> .ColorIndex = 36
> .Pattern = xlSolid
> End With
> Range("a" & r).Select
> Set RngFound = rngToSearch.FindNext
> Loop Until RngFound Is Nothing
> End If
> End Sub

Thank you Tom, I appreciate the help.

"Tom Ogilvy" wrote:

> sheets("c").Select
> Set wks = ActiveSheet
> Set rngToSearch = wks.Columns(1)
> Set RngFound = rngToSearch.Find(what:="new", LookIn:=xlValues,
> lookat:=xlWhole)
> If RngFound Is Nothing Then
> MsgBox "Nothing"
> Else
> sAddr = RngFound.Address
> Do
> r = RngFound.Row
> Range("a" & r, "z" & r).Select
> With Selection.Interior
> .ColorIndex = 36
> .Pattern = xlSolid
> End With
> Range("a" & r).Select
> Set RngFound = rngToSearch.FindNext(rngFound)
> Loop Until RngFound Is Nothing or RngFound.Address = _
> sAddr
> End If
> End Sub
>
> --
> Regards,
> Tom Ogilvy
>
> "John" <John@discussions.microsoft.com> wrote in message
> news:E2A5624E-6E67-46B8-AACB-CDF640EA4F81@microsoft.com...
> > Can someone tell me why this won't find the next value in the loop... it
> > stays on the first one and crashes my excel...
> >
> > heets("c").Select
> > Set wks = ActiveSheet
> > Set rngToSearch = wks.Columns(1)
> > Set RngFound = rngToSearch.Find(what:="new", LookIn:=xlValues,
> > lookat:=xlWhole)
> > If RngFound Is Nothing Then
> > MsgBox "Nothing"
> > Else
> > Do
> > r = RngFound.Row
> > Range("a" & r, "z" & r).Select
> > With Selection.Interior
> > .ColorIndex = 36
> > .Pattern = xlSolid
> > End With
> > Range("a" & r).Select
> > Set RngFound = rngToSearch.FindNext
> > Loop Until RngFound Is Nothing
> > End If
> > End Sub
>
>
>

hi,
i copied your code and tried to run it. got caught in an endless loop. so i
re-wrote i a little. your main problem was in the do loop.

Sub macfindit()
Dim wks As Worksheet
Dim rngToSearch As Range
Dim rngFound As Range
Dim r As Long
Dim addr As String

'Sheets("Sheet1").Select
Set wks = ActiveSheet
Set rngToSearch = wks.Columns(1)
Set rngFound = rngToSearch.Find(what:="new", _
After:=Range("A1"), _
LookIn:=xlValues, _
SearchOrder:=xlColumns, _
SearchDirection:=xlNext, _
lookat:=xlWhole)

If rngFound Is Nothing Then
MsgBox "Nothing"
Exit Sub
Else
addr = rngFound.Address
Do
r = rngFound.Row
Range("a" & r, "z" & r).Select
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
Range("a" & r).Select
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = addr
End If

End Sub

regards
FSt1
"John" wrote:

> Can someone tell me why this won't find the next value in the loop... it
> stays on the first one and crashes my excel...
>
> heets("c").Select
> Set wks = ActiveSheet
> Set rngToSearch = wks.Columns(1)
> Set RngFound = rngToSearch.Find(what:="new", LookIn:=xlValues,
> lookat:=xlWhole)
> If RngFound Is Nothing Then
> MsgBox "Nothing"
> Else
> Do
> r = RngFound.Row
> Range("a" & r, "z" & r).Select
> With Selection.Interior
> .ColorIndex = 36
> .Pattern = xlSolid
> End With
> Range("a" & r).Select
> Set RngFound = rngToSearch.FindNext
> Loop Until RngFound Is Nothing
> End If
> End Sub

Thanks! That works as well.

"FSt1" wrote:

> hi,
> i copied your code and tried to run it. got caught in an endless loop. so i
> re-wrote i a little. your main problem was in the do loop.
>
> Sub macfindit()
> Dim wks As Worksheet
> Dim rngToSearch As Range
> Dim rngFound As Range
> Dim r As Long
> Dim addr As String
>
> 'Sheets("Sheet1").Select
> Set wks = ActiveSheet
> Set rngToSearch = wks.Columns(1)
> Set rngFound = rngToSearch.Find(what:="new", _
> After:=Range("A1"), _
> LookIn:=xlValues, _
> SearchOrder:=xlColumns, _
> SearchDirection:=xlNext, _
> lookat:=xlWhole)
>
> If rngFound Is Nothing Then
> MsgBox "Nothing"
> Exit Sub
> Else
> addr = rngFound.Address
> Do
> r = rngFound.Row
> Range("a" & r, "z" & r).Select
> With Selection.Interior
> .ColorIndex = 36
> .Pattern = xlSolid
> End With
> Range("a" & r).Select
> Set rngFound = rngToSearch.FindNext(rngFound)
> Loop Until rngFound.Address = addr
> End If
>
> End Sub
>
> regards
> FSt1
> "John" wrote:
>
> > Can someone tell me why this won't find the next value in the loop... it
> > stays on the first one and crashes my excel...
> >
> > heets("c").Select
> > Set wks = ActiveSheet
> > Set rngToSearch = wks.Columns(1)
> > Set RngFound = rngToSearch.Find(what:="new", LookIn:=xlValues,
> > lookat:=xlWhole)
> > If RngFound Is Nothing Then
> > MsgBox "Nothing"
> > Else
> > Do
> > r = RngFound.Row
> > Range("a" & r, "z" & r).Select
> > With Selection.Interior
> > .ColorIndex = 36
> > .Pattern = xlSolid
> > End With
> > Range("a" & r).Select
> > Set RngFound = rngToSearch.FindNext
> > Loop Until RngFound Is Nothing
> > End If
> > End Sub