How do I use conditional formatting to change subtotals row format
 

I would like to change the background color of subtotal rows in Excel to
highlight the rows. I am using the display subtoals function from the menu.
I'm sure there is a way to do it using conditional formatting, but I haven't
been able to figure it out.

Thanks for your help.

Hi ken

one option is to conditionally format for values over the largest number in
your dataset ... as long as all the subtotals will be greater than that
number
given the following data
............A................B.................... ..C.
1......Month.......Region.................Sales
2......Jan............North.................50000
3......Feb...........North.................55000
4......Jan...........South...................35000
5......Jan.......... South...................40000

before you subtotal - select all your rows, from row 1
and choose format / conditional formatting
choose
formula is
type
=$C170000
select a format / okay & okay
now subtotal

Cheers
JulieD

"Ken Peterson" <Ken wrote in message
...
I would like to change the background color of subtotal rows in Excel to
highlight the rows. I am using the display subtoals function from the
menu.
I'm sure there is a way to do it using conditional formatting, but I
haven't
been able to figure it out.

Thanks for your help.

Hi JulieD,

Thanks for your excellent response and for opening my eyes. I didn't use
your exact suggestion, but it gave me another idea that worked just as well.

I used the following: =(MID($A4,4,6))="Total". The first three characters in
my worksheey would always be two characters plus a space, so I know the
fourth character will always be "Total".

For the Grand Total, I used: =(MID($A4,1,5))="Grand" to apply a different
format.

Thanks again,
Ken

"JulieD" wrote:

Hi ken

one option is to conditionally format for values over the largest number in
your dataset ... as long as all the subtotals will be greater than that
number
given the following data
............A................B.................... ..C.
1......Month.......Region.................Sales
2......Jan............North.................50000
3......Feb...........North.................55000
4......Jan...........South...................35000
5......Jan.......... South...................40000

before you subtotal - select all your rows, from row 1
and choose format / conditional formatting
choose
formula is
type
=$C170000
select a format / okay & okay
now subtotal

Cheers
JulieD

"Ken Peterson" <Ken wrote in message
...
I would like to change the background color of subtotal rows in Excel to
highlight the rows. I am using the display subtoals function from the
menu.
I'm sure there is a way to do it using conditional formatting, but I
haven't
been able to figure it out.

Thanks for your help.

Hi Ken

glad you found a workable solution

Cheers
JulieD

"Ken Peterson" wrote in message
...
Hi JulieD,

Thanks for your excellent response and for opening my eyes. I didn't use
your exact suggestion, but it gave me another idea that worked just as
well.

I used the following: =(MID($A4,4,6))="Total". The first three characters
in
my worksheey would always be two characters plus a space, so I know the
fourth character will always be "Total".

For the Grand Total, I used: =(MID($A4,1,5))="Grand" to apply a different
format.

Thanks again,
Ken

"JulieD" wrote:

Hi ken

one option is to conditionally format for values over the largest number
in
your dataset ... as long as all the subtotals will be greater than that
number
given the following data
............A................B.................... ..C.
1......Month.......Region.................Sales
2......Jan............North.................50000
3......Feb...........North.................55000
4......Jan...........South...................35000
5......Jan.......... South...................40000

before you subtotal - select all your rows, from row 1
and choose format / conditional formatting
choose
formula is
type
=$C170000
select a format / okay & okay
now subtotal

Cheers
JulieD

"Ken Peterson" <Ken wrote in message
...
I would like to change the background color of subtotal rows in Excel to
highlight the rows. I am using the display subtoals function from the
menu.
I'm sure there is a way to do it using conditional formatting, but I
haven't
been able to figure it out.

Thanks for your help.