I have a sheet full of grouped rows with subtotal rows at the
bottom of each group/set. When all the groups are collapsed,
I see only the subtotal rows. When I expand a group, I have
a bit of trouble visually distinguishing that group's subtotal
row from others just below it. I would like to code some
sort of event code that changes that subtotal row's color if
the group is expanded. Is this possible within reason?
I'm using Excel 2002.

--
dman

Since expanding and collapsing is not a recognized event this is a real up
hill battle. How about just applying a Format - AutoFormat to the subtotals.
That should help you get some physical deliniation between data and
subtotals...
--
HTH...

Jim Thomlinson


"Dallman Ross" wrote:

I have a sheet full of grouped rows with subtotal rows at the
bottom of each group/set. When all the groups are collapsed,
I see only the subtotal rows. When I expand a group, I have
a bit of trouble visually distinguishing that group's subtotal
row from others just below it. I would like to code some
sort of event code that changes that subtotal row's color if
the group is expanded. Is this possible within reason?
I'm using Excel 2002.

--
dman

During your selection of totals, you can change a
Ctrl+A (select all) though you may have to play with hitting
that a few times in newer versions of Excel.

Then use ALT+; (semicolon) to
Select only visible cells in the current selection
our use the toolbar button that has four black rectangles on it.
http://www.mvps.org/dmcritchie/excel/shortx2k.htm

Then any formatting you apply will only apply to those visible cells.

--
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Jim Thomlinson" wrote in message
...
Since expanding and collapsing is not a recognized event this is a real up
hill battle. How about just applying a Format - AutoFormat to the subtotals.
That should help you get some physical deliniation between data and
subtotals...
--
HTH...

Jim Thomlinson


"Dallman Ross" wrote:

I have a sheet full of grouped rows with subtotal rows at the
bottom of each group/set. When all the groups are collapsed,
I see only the subtotal rows. When I expand a group, I have
a bit of trouble visually distinguishing that group's subtotal
row from others just below it. I would like to code some
sort of event code that changes that subtotal row's color if
the group is expanded. Is this possible within reason?
I'm using Excel 2002.

--
dman

In , David McRitchie
spake thusly:

During your selection of totals, you can change a
Ctrl+A (select all) though you may have to play with hitting
that a few times in newer versions of Excel.

Thanks, David. I'm not sure what you mean by "change a Ctrl-A";
Ctrl-A selects the entire sheet regardless. I don't normally
select the subtotals row, I just click on a plus sign and expand
the group rows. The subtotal row was and remains visible just
below. It's just that other subtotal rows are all crammed
together just below that, and visually it's not the best for
instantly distinguishing which one should have my attention at
the bottom of a variable-length group of rows that just got
expanded.

In any case, when I try Alt-; , it says only visible cells are
already being displayed.

Then use ALT+; (semicolon) to
Select only visible cells in the current selection
our use the toolbar button that has four black rectangles on it.
http://www.mvps.org/dmcritchie/excel/shortx2k.htm

I'll look at your site. I just looked at all the toolbar buttons,
including the non-displayed ones, but I don't see the one you mean.

Then any formatting you apply will only apply to those visible cells.

I have the formatting the way I want it already for all the cells.
I just thought when I click the plus sign to open a collapsed group
of rows, the row just below -- which is my subtotal row -- could
change its color.

By the way, I see you quoted another poster, but I don't see his post
in the thread on my news server. I will respond to him here as well.


"Jim Thomlinson" wrote in
message ...

Since expanding and collapsing is not a recognized event this is a real up
hill battle. How about just applying a Format - AutoFormat to the subtotals.
That should help you get some physical deliniation between data and
subtotals...

Jim, thanks -- as you see above, your post did not make it to my
server. Not sure why. But I already have the formatting fine.
I merely want the color to change for my convenience. Here is my
thought on events: The event would be that the row just above the
subtotal row is now visible instead of hidden.

Thanks for any more ideas,
dman

================================================== ==========
"Dallman Ross" wrote:

I have a sheet full of grouped rows with subtotal rows at the
bottom of each group/set. When all the groups are collapsed,
I see only the subtotal rows. When I expand a group, I have
a bit of trouble visually distinguishing that group's subtotal
row from others just below it. I would like to code some
sort of event code that changes that subtotal row's color if
the group is expanded. Is this possible within reason?
I'm using Excel 2002.

Unfortunately Ctrl+A does not always select the entire
worksheet starting with Excel 2003 and botches up
a shortcut that is so cross application.
http://www.mvps.org/dmcritchie/excel...x2k.htm#foobar

--
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Dallman Ross" <dman@localhost. wrote in message ...
In , David McRitchie
spake thusly:

During your selection of totals, you can change a
Ctrl+A (select all) though you may have to play with hitting
that a few times in newer versions of Excel.

Thanks, David. I'm not sure what you mean by "change a Ctrl-A";
Ctrl-A selects the entire sheet regardless. I don't normally
select the subtotals row, I just click on a plus sign and expand
the group rows. The subtotal row was and remains visible just
below. It's just that other subtotal rows are all crammed
together just below that, and visually it's not the best for
instantly distinguishing which one should have my attention at
the bottom of a variable-length group of rows that just got
expanded.

In any case, when I try Alt-; , it says only visible cells are
already being displayed.

Then use ALT+; (semicolon) to
Select only visible cells in the current selection
our use the toolbar button that has four black rectangles on it.
http://www.mvps.org/dmcritchie/excel/shortx2k.htm

I'll look at your site. I just looked at all the toolbar buttons,
including the non-displayed ones, but I don't see the one you mean.

Then any formatting you apply will only apply to those visible cells.

I have the formatting the way I want it already for all the cells.
I just thought when I click the plus sign to open a collapsed group
of rows, the row just below -- which is my subtotal row -- could
change its color.

By the way, I see you quoted another poster, but I don't see his post
in the thread on my news server. I will respond to him here as well.


"Jim Thomlinson" wrote in
message ...

Since expanding and collapsing is not a recognized event this is a real up
hill battle. How about just applying a Format - AutoFormat to the subtotals.
That should help you get some physical deliniation between data and
subtotals...

Jim, thanks -- as you see above, your post did not make it to my
server. Not sure why. But I already have the formatting fine.
I merely want the color to change for my convenience. Here is my
thought on events: The event would be that the row just above the
subtotal row is now visible instead of hidden.

Thanks for any more ideas,
dman

================================================== ==========
"Dallman Ross" wrote:

I have a sheet full of grouped rows with subtotal rows at the
bottom of each group/set. When all the groups are collapsed,
I see only the subtotal rows. When I expand a group, I have
a bit of trouble visually distinguishing that group's subtotal
row from others just below it. I would like to code some
sort of event code that changes that subtotal row's color if
the group is expanded. Is this possible within reason?
I'm using Excel 2002.

Thanks, Dave. In any case, I can simply my request drastically:

Can someone show me some sample event code to change a row's color
if the row above it moves from hidden to visible?

I think, all-in-all, that's pretty much all I'd need to do what
I want here.

--
dman

In , David McRitchie
spake thusly:

Unfortunately Ctrl+A does not always select the
entire worksheet starting with Excel 2003 and
botches up a shortcut that is so cross application.
http://www.mvps.org/dmcritchie/excel...x2k.htm#foobar

Hiding or unhiding a row or column is not an event that can
be checked for, see the top of the page.
http://www.mvps.org/dmcritchie/excel/event.htm
besides it can be done by hiding row or done by using subtotal
buttons and I don't think they are the same.

Start your formatting by formatting in this order from
most minor to the major total.
no levels, full page -- probably nothing to format
level 3 total -- select visible cells only, and format
level 2 total -- select visible cells only, and format
level 1 total -- select visible cells only, and format

--
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Dallman Ross" <dman@localhost. wrote in message ...
Thanks, Dave. In any case, I can simply my request drastically:

Can someone show me some sample event code to change a row's color
if the row above it moves from hidden to visible?

I think, all-in-all, that's pretty much all I'd need to do what
I want here.

--
dman

In , David McRitchie
spake thusly:

Unfortunately Ctrl+A does not always select the
entire worksheet starting with Excel 2003 and
botches up a shortcut that is so cross application.
http://www.mvps.org/dmcritchie/excel...x2k.htm#foobar

You might find this macro helpful by modifying it
http://groups.google.com/groups?thre....microsoft.com

My toolbars page, simply show examples of creating buttons,
no macro to help you, but it is/was part of your question.

Maybe it would be easier to just bold/color those rows with subtotals in them.

You can do it manually:
Collapse the data so you only see the rows you want specially formatted.
Select all the table
edit|goto|special|visible cells only
apply the formatting to those visible cells.

If you aren't using format|conditional formatting, you can pick out a column
that has a the Total or Count or Average (whatever subtotal you used) word in
it.

Then select the whole range and do
format|conditional formatting
formula is
=countif($a1,"*total")0
Give it a pretty format.

(Column A had my category and I used Sum in Data|subtotal)

ps. when you get xl2003+, you might want to try
selecting the range (avoid the headers (in row 1 for me)
Format|Conditional formatting:
formula is:
=subtotal(103,$A1)0

The ability for subtotal to work on manually hidden rows was added in xl2003.





Dallman Ross wrote:

I have a sheet full of grouped rows with subtotal rows at the
bottom of each group/set. When all the groups are collapsed,
I see only the subtotal rows. When I expand a group, I have
a bit of trouble visually distinguishing that group's subtotal
row from others just below it. I would like to code some
sort of event code that changes that subtotal row's color if
the group is expanded. Is this possible within reason?
I'm using Excel 2002.

--
dman

--

Dave Peterson

Thanks very much for great help, David and others here.
I will be reading through this material this weekend. It was
a rather hellish week in the stock market, in case you didn't
notice -- so it has taken me a bit of time to get back to this.
But I did much appreciate the input.

Dallman
------------------------------------------------------------
In , David McRitchie
spake thusly:

Hiding or unhiding a row or column is not an event that can
be checked for, see the top of the page.
http://www.mvps.org/dmcritchie/excel/event.htm
besides it can be done by hiding row or done by using subtotal
buttons and I don't think they are the same.

Start your formatting by formatting in this order from
most minor to the major total.
no levels, full page -- probably nothing to format
level 3 total -- select visible cells only, and format
level 2 total -- select visible cells only, and format
level 1 total -- select visible cells only, and format

In , David McRitchie
spake thusly:

Hiding or unhiding a row or column is not an event that can
be checked for, see the top of the page.
http://www.mvps.org/dmcritchie/excel/event.htm
besides it can be done by hiding row or done by using subtotal
buttons and I don't think they are the same.

David, thank you. I was saving this until I had time to study
it and concentrate. That took about 12 days to happen, because
my work has been extremely harried and I had some deadlines going.

In any case, I've looked further at this now, and your and others'
help with my question is appreciated. I'm meanwhile re-doing the
entire set of sheets for this task, also. But the knowledge
you're providing is extremely valuable.

=dman=

======================================
Start your formatting by formatting in this order from
most minor to the major total.
no levels, full page -- probably nothing to format
level 3 total -- select visible cells only, and format
level 2 total -- select visible cells only, and format
level 1 total -- select visible cells only, and format

In , David McRitchie
spake thusly:

You might find this macro helpful by modifying it
http://groups.google.com/groups?thre....microsoft.com

My toolbars page, simply show examples of creating buttons,
no macro to help you, but it is/was part of your question.

Very interesting indeed. I am studying it. Thank you.

=dman=

In , Dave Peterson
spake thusly:

Maybe it would be easier to just bold/color those rows with
subtotals in them.

You can do it manually:
Collapse the data so you only see the rows you want specially
formatted.
Select all the table
edit|goto|special|visible cells only
apply the formatting to those visible cells.

Yes, thanks, Dave; I appreciate the ideas. It so happens I
already implemented both of these. :-)

If you aren't using format|conditional formatting, you can pick
out a column that has a the Total or Count or Average (whatever
subtotal you used) word in it.

Then select the whole range and do
format|conditional formatting
formula is
=countif($a1,"*total")0
Give it a pretty format.

And that's already been done as well. But I'm feeling in good
company to have thought of these things you're proposing! <vbg

I'll keep your mention of the xl2003+ stuff in mind as well. Thank
you again.

=dman=

=======================
ps. when you get xl2003+, you might want to try
selecting the range (avoid the headers (in row 1 for me)
Format|Conditional formatting:
formula is:
=subtotal(103,$A1)0

The ability for subtotal to work on manually hidden rows was
added in xl2003.


Dallman Ross wrote:

I have a sheet full of grouped rows with subtotal rows at the
bottom of each group/set. When all the groups are collapsed,
I see only the subtotal rows. When I expand a group, I have
a bit of trouble visually distinguishing that group's subtotal
row from others just below it. I would like to code some
sort of event code that changes that subtotal row's color if
the group is expanded. Is this possible within reason?
I'm using Excel 2002.

--
dman