Hi again Jhyatt,

I don't think that you can set a union on multiple sheets or at least I
don't know how. What I have done is modify the code to step through all the
worksheets and set the interior color of the found cells to Yellow just to
give you an example of stepping through the worksheets.

Further to what JLG Whiz said, I am intrigued as to why you want to set a
union of the found cells or is this just a training example?

Sub test()
Call AddName("Pear")
End Sub

Public Sub AddName(ByVal Fruit As String)
Dim ws As Worksheet
Dim wks As Worksheet
Dim rngToSearch As Range
Dim rngFound As Range
Dim strFirstAddress As String

'Repeat for each worksheet in workbook
For Each wks In Worksheets
Set rngToSearch = wks.Columns("A")

Set rngFound = rngToSearch.Find(What:=Fruit, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

If Not rngFound Is Nothing Then
strFirstAddress = rngFound.Address
Do
'Set the interior color of found cell to Yellow
rngFound.Interior.ColorIndex = 6
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress
End If
Next wks

End Sub


Regards,

OssieMac