Change row color on event

D

Dallman Ross

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.
 
G

Guest

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...
 
D

David McRitchie

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.
 
D

Dallman Ross

David McRitchie said:
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.
Click to expand...

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
Click to expand...

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.
Click to expand...

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.


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...
Click to expand...

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

============================================================
 
D

Dallman Ross

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.
 
D

David McRitchie

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
 
D

Dave Peterson

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.
 
D

Dallman Ross

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
 
D

Dallman Ross

David McRitchie said:
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.
Click to expand...

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=

======================================
 
D

Dallman Ross

Dave Peterson said:
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.
Click to expand...

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.
Click to expand...

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=

=======================
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SUBTOTAL 2
subtotal filter with growing range 2
Changing row color for each change in col A 3
Additonal info into newly created Subtotal row 1
Recalculating Subtotals 2
Cell Protection - Subtotal 1
Group Rows by Subtotal or Count 1
Subtotal Formatting 2

Top