Try the below..will certainly take you in the right direction

Sub Macro()
Dim varFound As Variant, varSearch As Variant
Dim strAddress As String, ws As Worksheet

varSearch = InputBox("Search string")
For Each ws In ActiveWorkbook.Sheets
ws.UsedRange.Interior.ColorIndex = 0
ws.UsedRange.Font.ColorIndex = 0
With ws.UsedRange
Set varFound = .Find(varSearch, LookIn:=xlValues)
If Not varFound Is Nothing Then
strAddress = varFound.Address
Do
varFound.Interior.ColorIndex = 3
varFound.Font.ColorIndex = 4
Set varFound = .FindNext(varFound)
Loop While Not varFound Is Nothing And _
varFound.Address < strAddress
End If
End With
Next
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"bawpie" wrote:

Hi,

I'm setting up a contact directory spreadsheet at the request of a
colleague, and it comprises of multiple sheets. I'm aware that I can simply
tell the user to select all of the worksheets and click c+F and the find all
option and it'll look for what they want, but I was wondering if it would be
possible to incorporate this as a macro, so the user needs only click one
button and type what they want to find in simply because I'm aware this
spreadsheet might be used by those with very little excel experience. I'd
like to simply automate the first few steps of the process i.e. 'select all
sheets', open find window, check 'find all' and then the user can simply type
in what they're looking for and click search.

I've tried recording it as a macro but the macro recorder doesn't pick up
any actions conducted in the find window, so any help on this would be much
appreciated!

Thankyou in advance,

Kirsty