How can I color only subtotal lines in Excel 2002?

D

dfw in NJ

I believe 2003 has this abiltiy; is there a macro that will facilitate the
selection and formatting of the subtotal likes themselves, and not the lines
of the list that is subtotaled?
 
J

Jim Thomlinson

You can apply an auto format using Format -> AutoFormat (don't do this on
list over 10,000 rows as it is painfully slow). If the list is over 10,000
the use a pivot table...
 
C

Carim

Hi Jim,

Based on what differentiates these lines have a go with Conditional
Formatting ...

HTH
Cheers
Carim
 
D

dfw in NJ

Jim: Thanks for the suggestion.

Using the lists in autoformat provides a solution.

I guess I have to go to conditional formatting (see Carim's suggestion) if
I want to change the color of the font..????

dfw
 
C

Carim

Hi,


Example of an event macro which colors 5 cells to its right if the word
"Subtotal" is entered in the reference range A1:A10 ... Adjust to
your needs

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1:A10")) Is Nothing Then
Exit Sub
Else
If Target.Value = "Subtotal" Then
Target.Offset(0, 1).Range("A1:E1").Interior.ColorIndex = 6
End If
End If
End Sub

HTH
Cheers
Carim
 
Top