I don't know why you need conditional formating when you can just highlight
the cell in red. Putting conditional formating in every cell will slow down
the workbook. I think the colorindex red was being over-written by the
pastespecial using format. Anyway I think using the find method maybe
better. See changes below.

Sub Formattotals()

Set c = Cells.Find(what:="TOTAL", _
MatchCase:=False, LookIn:=xlValues, lookat:=xlPart)
If Not c Is Nothing Then
FirstAddr = c.Address
Do
If Application.Intersect(c, Range("A1")) Then
c.PasteSpecial _
Paste:=xlPasteFormats
c.Interior.ColorIndex = 3
Range("A1").Copy
Set c = Cells.FindNext(after:=c)
End If
Loop While Not c Is Nothing And c.Address < FirstAddr
End If
End Sub

"Tree" wrote:

Joel - thank you, but this did not work quite the way expected.. it did apply
formatting but not to all total rows.. I'm not sure how it was discerning
which row to apply the formatting to? It didn't seem to have any kind of
discipline.. and it was not the formatting I wanted.. it put bold thick lines
on every row and changed the date column to numbers and not dates and it did
not apply the coloring to the rows with the word TOTAL in them.. and we will
need to "end" the paste command within the macro.. it finds the last cell and
stops, but it is still "active copying" because it has that "Select
Destination and press ENTER or choose Paste" message in the bottom left hand
corner..

I wish there was a way I could "show" you the result but I guess I can only
tell you.. plus, I wanted to know how to get this saved once it's working so
that I can call it up anytime I need it.. would that be saving it to the
xlstart folder?
Here is exactly what I am using for the macro code:

Sub Formattotals() - NOTE OUTSIDE OF THE MACRO - I had to add this line?
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

Again, thank you so much for your time and guidance!





"Joel" wrote:

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.