Alternate row colouring that also works when sheet is filtered?

[StargateFanNotAtHome]

I usually use the common conditional formatting code to produce
alternate row colouring: =MOD(ROW(),2)=0

This doesn't work when the sheet is filtered. i.e., in a worksheet
where we filter by a YES/NO column so that just the YES rows show up,
the row colouring is no longer alternating. It retains the colour
from its pre-filtered state so that we gets clumps of colour
together. No good.

How can we get alternate row colouring even on filtered worksheets,
pls?

 

[Gord Dibben]

=MOD(SUBTOTAL(3,$A1:$A$2),2)=0


Gord Dibben MS Excel MVP

 

[StargateFanNotAtHome]

=MOD(SUBTOTAL(3,$A1:$A$2),2)=0

Gord Dibben  MS Excel MVP

[snip]

Hi!

I'm sorry to say that this didn't work for me. I must be doing
something wrong. Even removing the absolute references didn't fix the
problem. I also fiddled around with the cell references but I'm
missing something, I think.

I get most of the rows as one solid colour with only a couple of rows
without. Very weird.

I'll try searching for this code. Perhaps some other messages will
help me to figure out what I'm doing wrong.

Thanks just the same! I'll keep trying. D

 

[StargateFanNotAtHome]

Perhaps this problem here lies with how the data is place this time
around (?). Here is the situation in this spreadsheet:

Header rows = A1 to T3

One condition only, and that is to have row colouring that works even
when filtered:
A4 to F92
K4 to R92

2 conditions; the cells needing row colouring to work even when
filtered is set as the 2nd condition:
G4 to J92

The row colouring as a third condition in these cells:
S4 to S92

This has bearing on the code, I imagine (??). I tried everything but
no modifications I made work. I get mostly cells of one entire colour
and have a handful that have no colour only but no alternating
colour. I haven't tried filtering to see what happens but it's code
that should work whether or not the sheet is filtered. And the
current unfiltered solid colouring that happens is no good. What am I
doing wrong, pls??

Thanks! D

 

[Dave Peterson]

Gord's formula worked ok for me.

But you'll have to adjust the references to match your layout.

Show all the data first.

Select A4:R92 (all the columns and all the rows in the data portion of your
range.

A4 should be the activecell in your selection.
Format|conditional Formatting
Formula is:
=MOD(SUBTOTAL(3,$A4:$A$4),2)=0

Give it a nice format.

This formula requires that column A have something in it--no empty cells!

And if you have columns that you don't want included in the shading, you can
select that range and remove the conditional formatting from that selection.

 

[StargateFanNotAtHome]

Well, I'm definitely doing something wrong. The modification to the
formula is exactly what I did myself earlier. It's just lack of self-
confidence of my part, perhaps, not trusting that I might be doing the
formula bit right. But that was one of the trials I did.

I get a solid colour from A4 to F92. The only thing different that I
did this time was to select A4 to F92 all at once, first, as you
suggested. I tend to do that part differently and by blocks of
selections at a time just to make sure that I don't goof up. However,
the end result was the same. In the unfiltered spreadsheet with all
data showing, I get a solid colour for _all_ rows in the pre-filtered
state. I don't get any alternating colour whatsoever. Filtering just
gives me more of the same solid colour ... (???)

If it might shed light on the issue, F92 has this in the formula for
conditional formatting after doing the above:
=MOD(SUBTOTAL(3,$A92:$A$4),2)=0

Does it not seem odd, or is it just me ... (?). But, then, what do I
know, Dave <g>. What can I be doing wrong, does this give a clue at
all?

D

 

[Gord Dibben]

Unfiltered worksheet.

Select A4:F92

Format>CF>Formula is: =MOD(SUBTOTAL(3,$A4:$A$5),2)=0

Works for me to band columns A:F

Preserves the banding through filtering.

From this description in your post

If it might shed light on the issue, F92 has this in the formula for
conditional formatting after doing the above:
=MOD(SUBTOTAL(3,$A92:$A$4),2)=0

Quite possibly you made an error and used =MOD(SUBTOTAL(3,$A4:$A$4),2)=0

It must be $A4:$A$5


Gord


On Tue, 10 Mar 2009 12:02:55 -0700 (PDT),

 

[StargateFanNotAtHome]

Unfiltered worksheet.

Select A4:F92

Format>CF>Formula is:  =MOD(SUBTOTAL(3,$A4:$A$5),2)=0

Works for me to band columns A:F

Preserves the banding through filtering.

<sigh> Again, getting the same thing. I get all solid-coloured
cells, no alternate colouring where each row is different colour.
They're all just a solid light yellow, which is the colour I have
selected in the CF ... ... (?)

From this description in your post

Yes, most likely! <g> I was just trying to edit the code to the best
of my somewhat meager flexible abilities since it turned out my header
rows consisted of more than one row and I know enough that code has to
be changed to accommodate different situations. But I'm really
stumped.

I selected A4 to F92, as suggested, and I applied the conditional
formatting via the usual FORMAT > CONDITIONAL FORMATTING ... > FORMULA
IS and then copy/pasted =MOD(SUBTOTAL(3,$A4:$A$5),2)=0 in (I never re-
type, always copy/paste exactly as given) and then got solid-coloured
rows as a result.

Why does it work for you and not for me? Filtered or unfiltered, rows
are one solid colour ...

It would be so nice to get this right. I'm hoping that one of you can
help me figure out what I'm doing wrong, because it's obviously me!
<g> The usual alternate row colouring is often enough since I don't
always deal with a sheet we need to filter but this current
spreadsheet is a rather large one and it would be so much easier for
my colleagues to read the printouts with filtered rows if they
maintained the alternate row colouring. As it stands now, the full
sheet prints out okay but any printouts with filtering come out with
clumps of different coloured rows.

Thanks, Gord! Really appreciate the help.

[snip]

 

[Dave Peterson]

You never said what was in column A. Is there something in the cells in that
column?

And if you try the same techique in a brand new workbook, does it work ok?

Unfiltered worksheet.

Select A4:F92

Format>CF>Formula is: =MOD(SUBTOTAL(3,$A4:$A$5),2)=0

Works for me to band columns A:F

Preserves the banding through filtering.

<sigh> Again, getting the same thing. I get all solid-coloured
cells, no alternate colouring where each row is different colour.
They're all just a solid light yellow, which is the colour I have
selected in the CF ... ... (?)

From this description in your post

Yes, most likely! <g> I was just trying to edit the code to the best
of my somewhat meager flexible abilities since it turned out my header
rows consisted of more than one row and I know enough that code has to
be changed to accommodate different situations. But I'm really
stumped.

I selected A4 to F92, as suggested, and I applied the conditional
formatting via the usual FORMAT > CONDITIONAL FORMATTING ... > FORMULA
IS and then copy/pasted =MOD(SUBTOTAL(3,$A4:$A$5),2)=0 in (I never re-
type, always copy/paste exactly as given) and then got solid-coloured
rows as a result.

Why does it work for you and not for me? Filtered or unfiltered, rows
are one solid colour ...

It would be so nice to get this right. I'm hoping that one of you can
help me figure out what I'm doing wrong, because it's obviously me!
<g> The usual alternate row colouring is often enough since I don't
always deal with a sheet we need to filter but this current
spreadsheet is a rather large one and it would be so much easier for
my colleagues to read the printouts with filtered rows if they
maintained the alternate row colouring. As it stands now, the full
sheet prints out okay but any printouts with filtering come out with
clumps of different coloured rows.

Thanks, Gord! Really appreciate the help.

[snip]

 

[StargateFanNotAtHome]

You never said what was in column A.  Is there something in the cells in that
column?

And if you try the same techique in a brand new workbook, does it work ok?
[snip]

Dave, hi!

Good point. Just tried this on a new worksheet but no luck. On a
brand-new worksheet with absolutely no data, I selected cells A4 to
F92 and then in conditional formatting put the formula above, =MOD
(SUBTOTAL(3,$A92:$A$5),2)=0 under the "formula is" option and only
changed the row colouring to light yellow, same as I usu. do but just
with this different formula. I get solid rows of light yellow
colour. This is with a brand new worksheet created just for this use
and with nothing in the cells at all! I then added some data in some
of the cells and put filtering in then filtered on that data and
resulting cells also solid coloured. Very puzzling if it's working
for you guys but not for me.

Re your query, my main sheet with data has only a numbering formula in
A4: =IF($B4<>"",SUBTOTAL(3,$B$4:$B4),""). The rest of the sheet just
has names and addresses, nothing out of the ordinary, really.

Taking a page from that "try it on a black sheet and see what happens"
book, I created a new sheet and then selected A2 to F92 and then put
the original code given above in the conditional formatting, =MOD
(SUBTOTAL(3,$A1:$A$2),2)=0, but it doesn't work either. End result is
still solid row colours, no alterating colours.

To tell you the truth, what I find funny is all the absolute cell
references. Does that mean that the alternate row colouring is based
on the relationship between $A in the first and always the second
absolute cell reference, in this case here: $A$2. That seems odd to
me, though, of course, who am I to question stuff ... <g> It just
seems a bit odd ... but, of course, but I'm not very good at all this
stuff ... <g>

They have Excel 2003 here at the office with SP3, if that helps
any ... (?)

Thanks! D

p.s., thanks. I'm house- and cat-sitting so have no internet access
after work so am only able to do this at work computer when there is
time. Sorry for some of the delays in responding because of all that.
<g>

 

[Dave Peterson]

I don't have any idea why it doesn't work for you--even in a test worksheet in a
new workbook.

But the absolute address in the formula:
=MOD(SUBTOTAL(3,$A$2:$A2),2)=0
(in A2 and dragged down)
means to start in A2 (always) and count through the cell with the formula)
(Gord's formula is slightly different)

Start a new workbook.
Put some test data in column A (enough to filter nicely).

Put this formula in B2:
=SUBTOTAL(3,$A$2:$A2)

Put this formula in C2:
=MOD(SUBTOTAL(3,$A$2:$A2),2)

Put this formula in D2:
=MOD(SUBTOTAL(3,$A$2:$A2),2)=0

And drag down.

Then you'll see what each does.

Now apply a filter and watch what happens.

=========
Why these formulas don't work for you for conditional formatting in xl2003 (I'm
using it, too) is a mystery to me.

You never said what was in column A. Is there something in the cells in that
column?

And if you try the same techique in a brand new workbook, does it work ok?
[snip]

Dave, hi!

Good point. Just tried this on a new worksheet but no luck. On a
brand-new worksheet with absolutely no data, I selected cells A4 to
F92 and then in conditional formatting put the formula above, =MOD
(SUBTOTAL(3,$A92:$A$5),2)=0 under the "formula is" option and only
changed the row colouring to light yellow, same as I usu. do but just
with this different formula. I get solid rows of light yellow
colour. This is with a brand new worksheet created just for this use
and with nothing in the cells at all! I then added some data in some
of the cells and put filtering in then filtered on that data and
resulting cells also solid coloured. Very puzzling if it's working
for you guys but not for me.

Re your query, my main sheet with data has only a numbering formula in
A4: =IF($B4<>"",SUBTOTAL(3,$B$4:$B4),""). The rest of the sheet just
has names and addresses, nothing out of the ordinary, really.

Taking a page from that "try it on a black sheet and see what happens"
book, I created a new sheet and then selected A2 to F92 and then put
the original code given above in the conditional formatting, =MOD
(SUBTOTAL(3,$A1:$A$2),2)=0, but it doesn't work either. End result is
still solid row colours, no alterating colours.

To tell you the truth, what I find funny is all the absolute cell
references. Does that mean that the alternate row colouring is based
on the relationship between $A in the first and always the second
absolute cell reference, in this case here: $A$2. That seems odd to
me, though, of course, who am I to question stuff ... <g> It just
seems a bit odd ... but, of course, but I'm not very good at all this
stuff ... <g>

They have Excel 2003 here at the office with SP3, if that helps
any ... (?)

Thanks! D

p.s., thanks. I'm house- and cat-sitting so have no internet access
after work so am only able to do this at work computer when there is
time. Sorry for some of the delays in responding because of all that.
<g>

 

[StargateFanNotAtHome]

Sorry for delay in getting back to the group, still house- and cat-
sitting with no internet access after working hours. Makes life
interesting.

Well, I can see how the formulas work and I think I know where the
problem lies.

Rather than using regular row numbering such as =ROW()-1, since this
spreadsheet needs to be filtered, I use this code (with cell
references modified, of course):
=IF($B4<>"",SUBTOTAL(3,$B$4:$B4),"")

That coding is perfect; it adjusts for filtering whereas =ROW()-1 does
not, yet doesn't "count" rows that are empty which is more or less
guaranteed by the
=IF($B4<>"",
part. Obviously the rest of the row besides B4 could have something
in it but as will all spreadsheets, that's unlikely. The rows are
usually empty if B4 remains empty (or whatever cell reference is put
in there, of course).

The code given up for row colouring that adjusts for filtering does so
_only_ with row number code that does _not_ adjust for filtering.

So that begs the question, is there alternate row colouring that
_does_ adjust for filtering that will allow me to use my row numbering
coding that also adjusts for filtering but that also accommodates
empty rows?

Regular row colouring that doesn't display filtered rows properly, =MOD
(ROW(),2)=0, works no matter what the contents of the cells are
whereas something like =MOD(SUBTOTAL(3,$A4:$A$4),2)=0 seems to very
much care about what is actually in the cells. Makes life difficult.
Any way to work around this?

In essence, we need row colouring and row numbering that isn't
absolute so that when data is filtered, we go row colouring and
numbering that reflects the current, real, filtered state not just
whatever the pre-filtered state is.

Thanks. D

 

[Dave Peterson]

I don't have any more suggestions.

(I can't make it so that the conditional formatting doesn't work.)

 

[StargateFanNotAtHome]

I don't have any more suggestions.

(I can't make it so that the conditional formatting doesn't work.)

[snip]

Really?? How odd.

Again, in XL2003 SP3, if you do the following, do you not get the same
results I do?:

1. New sheet.
2. Print area = A1 to B30.
3. - Select A2 to B30 and enter conditional formatting =MOD(SUBTOTAL
(3,$A2:$A$2),2)=0.
- Select row colour of, say, light yellow.
4. Close conditional formatting.

For me, the above steps 1-4 give a solid colour even WITHOUT ANY DATA
from A2 to B30.

And the following happens ...:
1. A2 to B30 start out solid light yellow colour.
2. I type "Anne" into A2: all colour disappears from all rows.
3. I type in "Peter" into A3: A3 to B30 turn to light yellow colour,
solid (no alternating).
4. A4, I type in "Anne": A3:B3 = light yellow, no colour anywhere
else.
5. A5 = Sam: rows 2 and 4 now retain alternating colour but rest of
spreadsheet below A5 turns solid.
6. Each additional name, no matter what it is (in other words, that
then contains data), row turns to alternate colouring. All empty rows
below have solid colour.

Does this not happen to you, too?

For me, this is not good coding. If I go back and again select A2 to
B30 and put in the regular alternate row colouring of =MOD(ROW(),2)=0
then _ALL_ the rows, empty or not, get alternate colouring and not
this ever-changing colour situation. I'm going to have to leave this
clunky =MOD(ROW(),2)=0 colour in even though the colouring gets out of
whack when the sheet is filtered. If there is no other possible
solution to get a result, then this is the first serious weakness that
I can remember coming across in Excel.

It's funny because the row numbering code that I mention above works
on filtered and non-filtered data just perfectly and also works only
when data is in a cell we designate so that, in essence, only rows
with data in them are counted. This is perfect. I realize that we're
using Excel in an advanced way, emulating a database type of situation
even though Excel is not geared for that function particularly , but
if we can't get a row colouring conditional formatting forumula, then
this is the only area where Excel isn't doing all it can as is usually
not the norm.

Anyway, thanks for everyone's help. It's always good to know what a
program can and can't do and though it's disappointing, Excel has
always managed to perform except for this one case (unless it's found
that something else is going on that can be fixed <g>).

Cheers. D

 

[Dave Peterson]

That didn't break it. It just did what it was supposed to.

=subtotal(3,...)
is the same as =counta(). It counts the number of non-empty cells in that
range.

Since it was an empty worksheet, all the cells were empty. That means that the
count was always 0. And that means that the result divided by 2 is always 0.

You need to use a column that always has something in it.

This was one of the conditions from a previous post:

"This formula requires that column A have something in it--no empty cells!"

I don't have any more suggestions.

(I can't make it so that the conditional formatting doesn't work.)

[snip]

Really?? How odd.

Again, in XL2003 SP3, if you do the following, do you not get the same
results I do?:

1. New sheet.
2. Print area = A1 to B30.
3. - Select A2 to B30 and enter conditional formatting =MOD(SUBTOTAL
(3,$A2:$A$2),2)=0.
- Select row colour of, say, light yellow.
4. Close conditional formatting.

For me, the above steps 1-4 give a solid colour even WITHOUT ANY DATA
from A2 to B30.

And the following happens ...:
1. A2 to B30 start out solid light yellow colour.
2. I type "Anne" into A2: all colour disappears from all rows.
3. I type in "Peter" into A3: A3 to B30 turn to light yellow colour,
solid (no alternating).
4. A4, I type in "Anne": A3:B3 = light yellow, no colour anywhere
else.
5. A5 = Sam: rows 2 and 4 now retain alternating colour but rest of
spreadsheet below A5 turns solid.
6. Each additional name, no matter what it is (in other words, that
then contains data), row turns to alternate colouring. All empty rows
below have solid colour.

Does this not happen to you, too?

For me, this is not good coding. If I go back and again select A2 to
B30 and put in the regular alternate row colouring of =MOD(ROW(),2)=0
then _ALL_ the rows, empty or not, get alternate colouring and not
this ever-changing colour situation. I'm going to have to leave this
clunky =MOD(ROW(),2)=0 colour in even though the colouring gets out of
whack when the sheet is filtered. If there is no other possible
solution to get a result, then this is the first serious weakness that
I can remember coming across in Excel.

It's funny because the row numbering code that I mention above works
on filtered and non-filtered data just perfectly and also works only
when data is in a cell we designate so that, in essence, only rows
with data in them are counted. This is perfect. I realize that we're
using Excel in an advanced way, emulating a database type of situation
even though Excel is not geared for that function particularly , but
if we can't get a row colouring conditional formatting forumula, then
this is the only area where Excel isn't doing all it can as is usually
not the norm.

Anyway, thanks for everyone's help. It's always good to know what a
program can and can't do and though it's disappointing, Excel has
always managed to perform except for this one case (unless it's found
that something else is going on that can be fixed <g>).

Cheers. D

 

[Pete_UK]

I used a slightly different formula in your step 3:

=AND(MOD(SUBTOTAL(3,$A2:$A$2),2)=0,$A2<>"")

so that the conditional formatting is not affected by blanks in column
A. The banding shows when you have data in column A, alternately white
and yellow - empty cells are always white, so you can have areas of
white with a block of empty cells. When you apply the filter you get
alternate rows coloured. The effect is more noticeable if you set up a
second condition (with say a green background) with a formula like:

=AND(MOD(SUBTOTAL(3,$A2:$A$2),2)=1,$A2<>"")

Maybe you would like to look into this effect.

Hope this helps.

Pete

I don't have any more suggestions.

(I can't make it so that the conditional formatting doesn't work.)

[snip]

Really??  How odd.

Again, in XL2003 SP3, if you do the following, do you not get the same
results I do?:

1.  New sheet.
2.  Print area = A1 to B30.
3.  -  Select A2 to B30 and enter conditional formatting =MOD(SUBTOTAL
(3,$A2:$A$2),2)=0.
    -  Select row colour of, say, light yellow.
4.  Close conditional formatting.

For me, the above steps 1-4 give a solid colour even WITHOUT ANY DATA
from A2 to B30.

And the following happens ...:
1.  A2 to B30 start out solid light yellow colour.
2.  I type "Anne" into A2:  all colour disappears from all rows.
3.  I type in "Peter" into A3:  A3 to B30 turn to light yellow colour,
solid (no alternating).
4.  A4, I type in "Anne":  A3:B3 = light yellow, no colour anywhere
else.
5.  A5 = Sam:  rows 2 and 4 now retain alternating colour but rest of
spreadsheet below A5 turns solid.
6.  Each additional name, no matter what it is (in other words, that
then contains data), row turns to alternate colouring.  All empty rows
below have solid colour.

Does this not happen to you, too?

For me, this is not good coding.  If I go back and again select A2 to
B30 and put in the regular alternate row colouring of =MOD(ROW(),2)=0
then _ALL_ the rows, empty or not, get alternate colouring and not
this ever-changing colour situation.  I'm going to have to leave this
clunky =MOD(ROW(),2)=0 colour in even though the colouring gets out of
whack when the sheet is filtered.  If there is no other possible
solution to get a result, then this is the first serious weakness that
I can remember coming across in Excel.

It's funny because the row numbering code that I mention above works
on filtered and non-filtered data just perfectly and also works only
when data is in a cell we designate so that, in essence, only rows
with data in them are counted.  This is perfect.  I realize that we're
using Excel in an advanced way, emulating a database type of situation
even though Excel is not geared for that function particularly , but
if we can't get a row colouring conditional formatting forumula, then
this is the only area where Excel isn't doing all it can as is usually
not the norm.

Anyway, thanks for everyone's help.  It's always good to know what a
program can and can't do and though it's disappointing, Excel has
always managed to perform except for this one case (unless it's found
that something else is going on that can be fixed <g>).

Cheers.  D

 

[ebloch]

Look at free ASAP Utilities http://www.asap-utilities.com/. I have never
tried it on a filtered worksheet but it might solve your problem.

Eric

 

[StargateFanNotAtHome]

I used a slightly different formula in your step 3:

=AND(MOD(SUBTOTAL(3,$A2:$A$2),2)=0,$A2<>"")

so that the conditional formatting is not affected by blanks in column
A. The banding shows when you have data in column A, alternately white
and yellow - empty cells are always white, so you can have areas of
white with a block of empty cells. When you apply the filter you get
alternate rows coloured. The effect is more noticeable if you set up a
second condition (with say a green background) with a formula like:

=AND(MOD(SUBTOTAL(3,$A2:$A$2),2)=1,$A2<>"")

Maybe you would like to look into this effect.

Hope this helps.

Pete

[snip]

Pete that seems to have done the job. I don't know as much as you
guys, as you all can probably very well tell <g>, and I would never
have figured out how to accommodate the empty cells. I strongly
suspect that this might be better code to use rather than the one that
doesn't account for empty cells because those are going to be
encountered more often than not! Empty cells happen in rows that are
ready for data to be added! Like, d'uh!! <lol> If it freaked me out
how the rows changed colour in that manner, it will freak others out
even more! <g> But having no colour until something is added in,
well, that's much easier to adapt to.

I tested out the above and so far, so good. Now going to go and edit
the big project. The "database" has quite a bit of data in it and we
have 4 major filters to do on it that will be made more easily dealt
with once the alternating rows work on the filters. <crossing
fingers>

I'll report back ...

Thanks. Much appreciated.

 

[Pete_UK]

Glad to be of help - keep us informed ...

Pete

 

[StargateFanNotAtHome]

Glad to be of help - keep us informed ...

Pete

[snip]

That is just pretty, freakin' amazing! <g>

I tested it yesterday but decided to wait till today to test again ...
fresh day, rested mind and all that. Well, I filtered every column
this morning and the code I now know has made the data just about as
easy to look through and work with as can be imagined. The alternate
row colouring works perfectly; so in conjunction with row numbering
that also adjusts for filtering and code that allows filtering to work
even on protected sheets, can't see how this could get much easier!

As an aside, I saved the sheet in my tips folder and I can foresee it
being the basis for most future Excel spreadsheets since it this
worksheet has the features that I use the most in Excel. Let's face
it, we often need to come up with some way to have a database of
information but Access just takes too long and much more skill than
most of us possess to use on a regular basis. Yes, I can set up
rudimentary databases in Access but they take a lot more time to do
up. An Excel spreadsheet takes care of most needs even though that's
not what it was written for, strictly speaking -- yet we all use it
for tracking and manipulating data. With these 3 bits of code that I
have plus a few other things, I have the best of everything: the
speed of set up and ease of use since most people understand Excel to
some degree (unlike Access), yet you can usually protect the data
enough that it's not as easily corrupted as an unprotected sheet would
otherwise be.

Thanks everyone! As I say so often, where would I be without all this
fabulous help! I'd still be able to function but nowhere near the
levels that I do now consistently.

Cheers! D