Thanks for taking the time to responsed Dave. It is amazing how many
different ways you can achieve the same thing in Excel.

"Dave Peterson" wrote:

You write worksheet (most people mean a single worksheet), but then you loop
through all the worksheets in the active workbook.

I'm gonna guess that you really wanted to loop through all the worksheets in
that workbook and find that string:

Option Explicit
Private Sub CommandButton1_Click()

Dim Wks As Worksheet
Dim myWord As String
Dim FoundCell As Range
Dim FirstAddress As String

myWord = InputBox("Please enter a Word")
If Trim(myWord) = "" Then
Exit Sub 'user hit cancel
End If

For Each Wks In Worksheets
Set FoundCell = Nothing
FirstAddress = ""
With Wks
'do the entire usedrange at once.
.UsedRange.Interior.ColorIndex = xlNone

Set FoundCell = .Cells.Find(what:=myWord, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If FoundCell Is Nothing Then
'not on this sheet
Else
FirstAddress = FoundCell.Address

Do
'do the work
With FoundCell.Interior
.ColorIndex = 6
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With

'look for more
Set FoundCell = .Cells.FindNext(after:=FoundCell)

If FoundCell Is Nothing Then
'this shouldn't happen
Exit Do
End If

If FoundCell.Address = FirstAddress Then
'back to the first cell, so exit the loop
Exit Do
End If
Loop
End If
End With
Next Wks
End Sub

Felixdecat wrote:

Hello, I have written the following code. The purpose is to find every
instance of a word in the worksheet, and then highlight it yellow. However,
it only finds the 1st instance, but does not carry on searching the rest of
the worksheet. Can anyone help me? The first part of the code clears the
previous search results.

Private Sub CommandButton1_Click()

Dim Wsht As Worksheet
Dim Rng As Range
Dim cl As Range
For Each Wsht In Worksheets
Set Rng = Wsht.UsedRange
For Each cl In Rng
With cl
If .Interior.ColorIndex = 6 Then
.Interior.ColorIndex = 0
End If
End With
Next cl
Next Wsht

Set Rng = Nothing
Set cl = Nothing

w = InputBox("Please enter a Word")

Cells.Find(What:=(w), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
End Sub

--

Dave Peterson
.