No need to repeat the statements, just need to find all the values first.

HTH,
Bernie
MS Excel MVP

Sub FindValues()
Dim c As Range
Dim d As Range
Dim firstAddress As String

'First, find all the cells
With Cells

Set c = .Find("Total", LookIn:=xlValues, lookAt:=xlWhole)

If Not c Is Nothing Then
Set d = c
firstAddress = c.Address
Else:
MsgBox "Not Found"
End
End If

Set c = .FindNext(c)
If Not c Is Nothing And c.Address < firstAddress Then
Do
Set d = Union(d, c)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

' Then do what you want with the offset of
' all the cells that have been found:
With d.Offset(0, 4)
.FormulaR1C1 = "=Sum(RC[-1]-RC[-2])"
.NumberFormat = "0.00"
.Font.Bold = True
With .Interior
.ColorIndex = 6
End With
End With

End Sub


"Frantic Excel-er" wrote in
message ...
Hi All,

I have the following code at the end of a long macro:

Columns("D:D").Find(What:="total").Select
ActiveCell.Offset(0, 4).Range("A1").Select
ActiveCell.FormulaR1C1 = "=Sum(RC[-1]-RC[-2])"
ActiveCell.NumberFormat = "0.00"
ActiveCell.Font.Bold = True
With ActiveCell.Interior
.ColorIndex = 6
End With

What I am doing is when the word "total" appears, it will go to the cell 4
columns to the right, and then do a formula and format the cell.

Question: How can I get this to repeat itself for each time the word
"total" appears. Each different time I run this macro, I may have
anywhere
from 1 to 100 totals in the respective worksheet. I think that I need to
either loop, or do a for..next statement, but I don't know how to get the
syntax right, or which would work better.

Any help would be greatly appreciated...thanks in advance...

Sara