I don't think you want to put conditional formating in every cell in the
worksheet because it will use up a lot of memory and slow down the workbook.
I put the formating only in the area where data is located.

Set LastCell = Cells.SpecialCells(xlCellTypeLastCell)
Set UsedArea = Range(Range("A1"), LastCell)
With Range("A1")
.FormatConditions.Delete
.FormatConditions.Add _
Type:=xlExpression, _
Formula1:="=COUNTIF(1:1,""*TOTAL*"")"
.FormatConditions(1).Interior.ColorIndex = 3
.Copy
UsedArea.PasteSpecial _
Paste:=xlPasteFormats
End With
End Sub

"Tree" wrote:

EXCEL 2007
Jacob helped me do this CF for a particular worksheet and it works great,
but I want to be able to "call" this into any worksheet whenever I need it..
we Use the Data Tab to run subtotals all the time - especially when we export
data from Quickbooks to modify reports in Excel..
I tried to Record the Macro in the Personal.xlb but that didn't go so well -
so any assistance from the experts would be so greatly appreciated..

1. Select cell A1. Then press (Ctrl+A) to select all cells
2. From menu FormatConditional Formatting
3. For Condition1Select 'Formula Is' and enter the below formula
=COUNTIF(1:1,"*TOTAL*")
4. Click Format ButtonPattern and select your color (say Red)
5. Hit OK
You can remove the * from the formula if you are looking for a whole cell
match.