Thanks a lot OssieMac!! That works great!

I did actually only want to do this for 1 worksheet. I cut and paste the
code for the colouring, so forgot to edit it for 1 worksheet only.

How do I change your code so it only applies to 1 sheet only?

Thanks again!



"OssieMac" wrote:

Because you are clearing the interior color index on all the worksheets I
have assumed that you also want to search all worksheets. I have included the
clear and set the color index in the one loop.

Private Sub CommandButton1_Click()

Dim Wsht As Worksheet
Dim Rng As Range
Dim cl As Range
Dim rngFind As Range
Dim firstAddress As String
Dim w As String

w = InputBox("Please enter a Word")

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

With Rng
Set rngFind = .Find(What:=(w), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If Not rngFind Is Nothing Then
firstAddress = rngFind.Address
Do
rngFind.Interior.ColorIndex = 6
Set rngFind = .FindNext(rngFind)

Loop While Not rngFind Is Nothing _
And rngFind.Address < firstAddress

End If
End With

Next Wsht

Set Rng = Nothing
Set cl = Nothing
Set rngFind = Nothing

End Sub

--
Regards,

OssieMac