Hi Carlton,

Is there a way to get it to run like a regular macro

Sure. From previous posts by you I incorrectly assumed you were lookiing to
have the cells colored dynamically. My error - apologies.

Try the following (in a standard module):

Public Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet
Dim rng As Range
Dim rCell As Range
Dim RngFound As Range

Set WB = Workbooks("BOOK2.xls") '<<===== CHANGE

Set rng = Range("A1:A10") '<<===== CHANGE

Application.ScreenUpdating = False

For Each rCell In rng
If Not IsEmpty(rCell) Then
For Each SH In WB.Worksheets
Set RngFound = SH.Cells.Find( _
what:=rCell.Value, _
After:=SH.Range("A1"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not RngFound Is Nothing Then
rCell.Interior.ColorIndex = 37
Exit For
End If
Next SH
End If
If RngFound Is Nothing Then rCell.Interior. _
ColorIndex = xlNone
Next rCell

Application.ScreenUpdating = True

End Sub


---
Regards,
Norman



"Carlton Patterson" wrote in message
...
Hi Norman,

This appears to be what I need. Is there a way to get it to run like a
regular macro? By 'run like a regular macro' I mean by running it like a
standard macro? At the moment it runs by opening up the worksheet.

Cheers mate

Carlton

*** Sent via Developersdex http://www.developersdex.com ***