Here's a way to color all cells on a sheet that are on the right of a cell containing the text Subtotal. I've used your worksheet name ("home"), and the color you chose:
Sub Mark_Subtotals()
Set myarea = Worksheets("home").UsedRange
With myarea
Set c = .Find("Subtotal", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
Range(c.Address).Offset(0, 1).Interior.ColorIndex = 40
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address<> firstAddress
End If
End With
End Sub
The advantage of the above method is that it uses the Find method instead of looping through all cells on the sheet.
This message was edited by hedrijw on 2002-08-29 06:33