Alternate row colouring that also works when sheet is filtered?

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?
0
3/9/2009 4:33:34 PM
excel 39879 articles. 2 followers. Follow

20 Replies
807 Views

Similar Articles

[PageSpeed] 39

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


Gord Dibben  MS Excel MVP

On Mon, 9 Mar 2009 09:33:34 -0700 (PDT), StargateFanNotAtHome@mailinator.com
wrote:

>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?

0
Gord
3/9/2009 4:57:03 PM
On Mar 9, 12:57=A0pm, Gord Dibben <gorddibbATshawDOTca> wrote:
> =3DMOD(SUBTOTAL(3,$A1:$A$2),2)=3D0
>
> Gord Dibben =A0MS 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.  :oD
0
3/10/2009 3:04:57 PM
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!  :oD
0
3/10/2009 3:33:41 PM
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@mailinator.com wrote:
> 
> 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!  :oD

-- 

Dave Peterson
0
petersod (12005)
3/10/2009 4:16:00 PM
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?

:oD
0
3/10/2009 7:02:55 PM
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@mailinator.com wrote:

>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?
>
>:oD

0
Gord
3/10/2009 8:15:41 PM
On Mar 10, 4:15=A0pm, Gord Dibben <gorddibbATshawDOTca> wrote:
> Unfiltered worksheet.
>
> Select A4:F92
>
> Format>CF>Formula is: =A0=3DMOD(SUBTOTAL(3,$A4:$A$5),2)=3D0
>
> 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
>
> >If it might shed light on the issue, F92 has this in the formula for
> >conditional formatting after doing the above:
> >=3DMOD(SUBTOTAL(3,$A92:$A$4),2)=3D0

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 =3DMOD(SUBTOTAL(3,$A4:$A$5),2)=3D0 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]
0
3/11/2009 3:02:27 PM
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?

StargateFanNotAtHome@mailinator.com wrote:
> 
> On Mar 10, 4:15 pm, Gord Dibben <gorddibbATshawDOTca> wrote:
> > 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
> >
> > >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
> 
> 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
0
petersod (12005)
3/11/2009 3:16:21 PM
On Mar 11, 11:16=A0am, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> You never said what was in column A. =A0Is there something in the cells i=
n that
> column?
>
> And if you try the same techique in a brand new workbook, does it work ok=
?

[snip]

> --
>
> Dave Peterson- Hide quoted text -

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, =3DMOD
(SUBTOTAL(3,$A92:$A$5),2)=3D0 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:  =3DIF($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, =3DMOD
(SUBTOTAL(3,$A1:$A$2),2)=3D0, 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!  :oD

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>


0
3/12/2009 1:53:02 PM
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.




StargateFanNotAtHome@mailinator.com wrote:
> 
> On Mar 11, 11:16 am, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> > 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 Peterson- Hide quoted text -
> 
> 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!  :oD
> 
> 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
0
petersod (12005)
3/12/2009 2:07:35 PM
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.  :oD
0
3/17/2009 6:53:16 PM
I don't have any more suggestions.  

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

StargateFanNotAtHome@mailinator.com wrote:
> 
> 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.  :oD

-- 

Dave Peterson
0
petersod (12005)
3/17/2009 7:02:07 PM
On Mar 17, 3:02 pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> 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.  :oD
0
3/17/2009 7:44:20 PM
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!"

StargateFanNotAtHome@mailinator.com wrote:
> 
> On Mar 17, 3:02 pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> > 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.  :oD

-- 

Dave Peterson
0
petersod (12005)
3/17/2009 8:12:07 PM
I used a slightly different formula in your step 3:

=3DAND(MOD(SUBTOTAL(3,$A2:$A$2),2)=3D0,$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:

=3DAND(MOD(SUBTOTAL(3,$A2:$A$2),2)=3D1,$A2<>"")

Maybe you would like to look into this effect.

Hope this helps.

Pete

On Mar 17, 7:44=A0pm, StargateFanNotAtH...@mailinator.com wrote:
> On Mar 17, 3:02 pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
>
> > I don't have any more suggestions.
>
> > (I can't make it so that the conditional formatting doesn't work.)
>
> [snip]
>
> Really?? =A0How odd.
>
> Again, in XL2003 SP3, if you do the following, do you not get the same
> results I do?:
>
> 1. =A0New sheet.
> 2. =A0Print area =3D A1 to B30.
> 3. =A0- =A0Select A2 to B30 and enter conditional formatting =3DMOD(SUBTO=
TAL
> (3,$A2:$A$2),2)=3D0.
> =A0 =A0 - =A0Select row colour of, say, light yellow.
> 4. =A0Close 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. =A0A2 to B30 start out solid light yellow colour.
> 2. =A0I type "Anne" into A2: =A0all colour disappears from all rows.
> 3. =A0I type in "Peter" into A3: =A0A3 to B30 turn to light yellow colour=
,
> solid (no alternating).
> 4. =A0A4, I type in "Anne": =A0A3:B3 =3D light yellow, no colour anywhere
> else.
> 5. =A0A5 =3D Sam: =A0rows 2 and 4 now retain alternating colour but rest =
of
> spreadsheet below A5 turns solid.
> 6. =A0Each additional name, no matter what it is (in other words, that
> then contains data), row turns to alternate colouring. =A0All empty rows
> below have solid colour.
>
> Does this not happen to you, too?
>
> For me, this is not good coding. =A0If I go back and again select A2 to
> B30 and put in the regular alternate row colouring of =3DMOD(ROW(),2)=3D0
> then _ALL_ the rows, empty or not, get alternate colouring and not
> this ever-changing colour situation. =A0I'm going to have to leave this
> clunky =3DMOD(ROW(),2)=3D0 colour in even though the colouring gets out o=
f
> whack when the sheet is filtered. =A0If 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. =A0This is perfect. =A0I realize that we'r=
e
> 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. =A0It'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. =A0:oD

0
pashurst (2576)
3/17/2009 8:33:20 PM
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@mailinator.com> wrote in message 
news:87a9ad85-bf48-498f-8bd9-ea6d0268c5eb@r18g2000vbi.googlegroups.com...
>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? 


0
ericNOSPAM (46)
3/17/2009 10:09:16 PM
On Mar 17, 4:33=A0pm, Pete_UK <pashu...@auditel.net> wrote:
> I used a slightly different formula in your step 3:
>
> =3DAND(MOD(SUBTOTAL(3,$A2:$A$2),2)=3D0,$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:
>
> =3DAND(MOD(SUBTOTAL(3,$A2:$A$2),2)=3D1,$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.
0
3/18/2009 7:09:16 PM
Glad to be of help - keep us informed ...

Pete

On Mar 18, 7:09=A0pm, StargateFanNotAtH...@mailinator.com wrote:
> Pete that seems to have done the job. =A0I 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. =A0I 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! =A0Empty cells happen in rows that are
> ready for data to be added! =A0Like, d'uh!! <lol> =A0If it freaked me out
> how the rows changed colour in that manner, it will freak others out
> even more! <g> =A0But 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. =A0Now going to go and edit
> the big project. =A0The "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. =A0<crossing
> fingers>
>
> I'll report back ...
>
> Thanks. =A0Much appreciated
0
pashurst (2576)
3/18/2009 8:14:21 PM
On Mar 18, 4:14=A0pm, Pete_UK <pashu...@auditel.net> wrote:
> 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!  :oD
0
3/19/2009 3:08:31 PM
Well, I'm glad to hear that it worked okay for you, though Dave and
Gord were the main contributors to this thread.

I share your sentiments about Access - maybe once I retire (several
years away yet) I might devote some time to getting to know it better.

Thanks for feeding back.

Pete

On Mar 19, 3:08=A0pm, StargateFanNotAtH...@mailinator.com wrote:
> On Mar 18, 4:14=A0pm, Pete_UK <pashu...@auditel.net> wrote:
>
> > 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. =A0Well, 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. =A0The 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. =A0Let'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. =A0Yes, I can set up
> rudimentary databases in Access but they take a lot more time to do
> up. =A0An 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. =A0With these 3 bits of code that I
> have plus a few other things, I have the best of everything: =A0the
> 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! =A0As I say so often, where would I be without all this
> fabulous help! =A0I'd still be able to function but nowhere near the
> levels that I do now consistently.
>
> Cheers! =A0:oD

0
pashurst (2576)
3/19/2009 7:31:48 PM
Reply:

Similar Artilces:

Hidden Rows #2
I am trying to unhide some hidden rows in a worksheet that was sent to me. Rows 1 and 2 are hidden. I've tried the simple route of selecting the entire sheet, pulling down the Format menu, and selecting unhide. I've also tried Ctrl-G (GoTo) and typing in A1. That doesn't work. The document is not protected nor are any of the cells (that I know of). Figz, Two other possibilities: 1) The window has frozen panes, so use windows / unfreeze panes 2) The row height is jsut very very small. Select all the cells using the button to the upper left of cell A1, then right click ...

Word integration that works ?
Does anyone have a link to a good solution in creating word documents from CRM, i.e. quotes etc. A solution where maybe it's possible to create it with a button and at the same time get an activity attached to the oppertunity. Jack -------------------------------------------------------------------------------- Jeg beskyttes af den gratis SPAMfighter til privatbrugere. Den har indtil videre sparet mig for at f� 14509 spam-mails Betalende brugere f�r ikke denne besked i deres e-mails. Hent en gratis SPAMfighter her. Suggest you look at Mail merge from mscrmaddons. Other than that i...

How OMPM Scanner (offscan) Filter by Access/Modified Date ?
Hello, I have problem to inventory excel files on very big file server, but I believe there are so many documents we no longer need to maintain. I want to skip files if the access date or modified date longer than 6 month, but don't see the OMPM providing feature about it. I currently running OMPM since 2 weeks ago and running out of time for reporting to my manager. Please help me, this is my critical assignment. -- Eldi Munggaran ...

OWA Exch 2003 Not Working
I am running Windows 2003 with Exchange 2003. I am trying to acces OWA and when I do, it says that the page is under maintenance. I have tried to do http://127.0.0.1/Exchange and also http://servername/Exchange and I get the same message. Did OWA not install right. On 14 Mar, 13:01, Jeff <J...@discussions.microsoft.com> wrote: > I am running Windows 2003 with Exchange 2003. I am trying to acces OWA and > when I do, it says that the page is under maintenance. I have tried to dohttp://127.0.0.1/Exchangeand alsohttp://servername/Exchangeand I get the > same message. Did OWA no...

outlook 2003sp3: applying the inbuilt junkmail filter on existing messages?
hi all, would like to apply the outlook junk mail filter, with settings set to 'high', on emails already in my inbox. is there any way of doing this? apologies if this has been posted before, I've made a couple of searches but can't find anything... many thanks! No, there's no way to apply the spam filter to already downloaded items. <spam@redo.net> wrote in message news:1137601837.257355.134230@g43g2000cwa.googlegroups.com... > hi all, > > would like to apply the outlook junk mail filter, with settings set to > 'high', on emails already in my...

One Entry to Multiple Rows
I have data that looks like this: X1 | Y1 Y2 Y3 Y4 X2 | Y4 Y5 Y6 Y7 And I need to get to: X1 | Y1 X1 | Y2 X1 | Y3 X1 | Y4 X2 | Y4 ...... etc. I can change the 2nd row's entries to more columns, but that doesn't seem to get me much closer to the needed format (and there are thousands of lines so I'd rather not do it manually). Any ideas? should do it. change mc to suit '===== Option Explicit Sub lineemup() Dim mc As Long Dim mr As Long Dim i As Long Dim lc As Long mc = 3 'col c mr = 1 For i = 1 To Cells(Rows.Count, mc).End(xlUp).Row lc ...

Connect/work offline/cancel message
In outlook 2003 connecting to SBS/Exchange 2003 server, get message: Exchange is currently in recovery mode you can connect, work offline, or cancel. This is only happening in a few computers on the network after implementing SBS 2003 with Outlook and Exchange 2003. ...

My Office 2007 spell checker is not working.
For some reason, Office 2007 is not finding my spelling errors (in Word or PowerPoint). It is not auto-correcting, highlighting errors, or finding errors when I manually run spell check. When I go to Word options, the dialog boxes indicate that it's on and that auto-correct is also on. What's happening? ...

Line Chart with dates in 5 day working week only
Hi, Trying to format a chart so that only the 5 working days of the week are displayed on the x axis. The source data only has the five days (e.g. 05/09/2005 down to 09/09/2005 and then on to 12/09/2005 down to 16/09/2005 etc etc) So I have missed out the weekend dates. When I create the line chart however, the weekend dates appear automatically and just show no point on the chart, therefore there is a longer line between Fridays and Mondays!! Hope this makes sense. Does anyone have any ideas on how to change this? I have tried looking at Tools-options-chart and cannot seem to turn...

Intelligent Message Filter Installation Failure
I've tried installing the Intelligent Message Filter on a Windows 2000 SP4 Exchange 2003 SP1 server. The installation fails at around 98% with the following error: There is a problem with this Windows Installer package. a program run as part of the setup did not finish as expected. contact your support personnel or package vendor. I tried downloading it again, and had the same results. Any ideas? Thanks ...

Excel 2003 Copy/Paste filtered column
I have a filtered column on my spreadsheet. I have copied the column, changed the figures and then tried to paste it back on to the filtered column. It is not copying over the original filtered column but rather over cells that have been filtered out. The worksheet/cells are not protected. What could the problem be? Kind Regards Heather That's the way pasting works. It'll hit the visible and hidden cells. Heather wrote: > > I have a filtered column on my spreadsheet. I have copied the column, > changed the figures and then tried to paste it back on to the filter...

Intelligent Message Filter (IMF) SCL for Junk E-mail being ignored
I have Exchange Server 2003 Enterprise SP1, and I recently downloaded and installed Intelligent Message Filter. In Message Delivery Properties -> Intelligent Message Filtering, I have - Gateway Blocking Configuration SCL 8 - Store Junk E-mail Configuration SCL 8 All users have Outlook 2003 Professional installed. Although I have SCL set to 8 for "Store Junk E-mail Configuration", Messages with SCL 6 and others with SCL as low as 3 are being sent to the Junk E-Mail folder. I am able to see the SCL of the emails because I installed an SCL Column via Forms using th...

Color not working
I am operating Windows 2000, using Office Pro 2003. I am no longer able to format my cells with colors, either shading or text coloring. I have run the repair feature, but it didn't work. Bold, Underline, and similar functions still work. Please help. Thanks. CC Have a look at this: Changes to Fill Color and Fill Pattern Are Not Displayed http://support.microsoft.com/?id=320531 Biff >-----Original Message----- >I am operating Windows 2000, using Office Pro 2003. I am >no longer able to format my cells with colors, either >shading or text coloring....

How do text capture tools work?
Here are two examples of this technology. http://www.textcapture.com/en/default.html http://www.deskperience.com/textcapture/ Sounds like they will have to involve OCR, which works fine for standard fonts but probably won't work with fancy fonts. Key here is to test them try a static control with a TextOut and change the fonts and see what happens. joe On Wed, 31 Oct 2007 15:02:04 -0500, "Peter Olcott" <NoSpam@SeeScreen.com> wrote: >Here are two examples of this technology. > > http://www.textcapture.com/en/default.html > http://www.deskp...

Remote Wipe Out Work with Treo 650?
Debating whether to get the Treo 650's with the ActiveSync built in vs. the PPC-6700 (both from Sprint) for our marketing reps here. Treo's are appealing due to how cheap they are now, and with the ActiveSync looks like you can sync up with xng2k3 sp2 (which we are implementing now). ActiveSync aside, can the Treo's be remotely wiped out with the Mobility Tool in xng2k3? Or is this feature only compatible with Windows Mobile OS? Anyone? Thx! No it can't. Remote wipe will only work with Windows mobile 5.0 devices with AKU 2.0 (formerly MSFP). Treo 650 is Palm OS. -- Be...

Online Quotes don't work anymore
Online quotes stopped working yesterday. I'm using MS Money 2001 standard. Any clues? There was a glitch for a bit that seems to be cleared up now... "Robert" <bob@flythrough.com> wrote in message news:069d01c357ca$e4ccc060$a301280a@phx.gbl... > Online quotes stopped working yesterday. I'm using MS > Money 2001 standard. Any clues? >-----Original Message----- >Online quotes stopped working yesterday. I'm using MS >Money 2001 standard. Any clues? >. >I have the same issue. It worked the first time but not since. Big problem for m...

Create individual files from a row
I have an excel file with several thousand entries, which contain data in several columns. I would like to be able to create an individual xml or html file for each row, but with predifined formatting around so Mr A bloggs, A street, A town, AA1 1AA Could become Abloggs.html <head></head> <name>Mr A bloggs</name> <street>A street</street> Any other info here as well </html> etc. Is this possible and any suggestions how? thanks, Graham. -- GrahamN ------------------------------------------------------------------------ GrahamN's Profile: ...

delegate email still not working
for exch 2003 on outlook 2003 newly supporting the above, i did not set up the initial install i created a new user with ad wizard, so it created email account, mailbox, etc i opened up a mail account for the new user in outlook express and was able to receive mail in oe for the new user, then i deleted that acct in outlook express then i added that new user account to an exisiting outlook2003 already connected to another exch mailbox, in the advanced add user section, that newly added delegate user has all permissions the outlook directory tree added the new user mailbox and exch accepted...

Determine number of rows with data
Hi I am using the macro below to pull some data from an external workbook. The 2 issues I need to sort are: 1. The number of rows in the external workbook can vary. How do I amend this code to pull all of the rows with data? 2. The number of rows in the autofill also may vary. How do I autofill only the number of rows required? i.e the number of rows in column A that contain data. 'Lookup Previous Month Sales Columns("K:K").Select Selection.NumberFormat = "General" Range("K4").Select Selection.FormulaArray = _ "=S...

Junk E-Mail filter unavailable in online mode
We had Outlook 2003 clients on an Exch 2000 server, and, as expected, the Junk E-Mail filter was not available when working in online mode. In cached mode it was working fine. Now, after upgrading the organization and server to Exch 2003, the Junk E-Mail filter is still not available in online mode. According to all the MS documentation this should be working. Any ideas? Thanks kindly, FV All of the documentation that I have know of says that it works on in Cache Mode. Can you give us a link to the documentation that you saw that shows it will work with online profiles? Thanks, -- Matth...

Exchange client alternative ?
I have an exchange server with about 100 users. I have three computers where I want that about 40-60 people should have access to checking their mail WITHOUT logging off/on. I want that there should be one username to the computer and some other authentication at the email client side. I was thinking of using Outlook Express & implementing identities. I'd like to know if anyone has some other ideas. Thanks Peretz Stern Hi Peretz, You could use Outlook Express with different identities or you could use Outlook 2000 or above with a seperate profile for each user. You could go ...

Copy/paste range of rows between 2 dates...
Hi! I have a sheet called data which act as a database. The column A has the dates. In order to create customized chart in a userform, for different range of data(i.e from column D, G and M...), I'd like to select a range of rows that are between 2 dates and create the charts accordingly. Or copy to range to another sheet and then create the charts. I am not so advanced in VBA and any help would be greatly appreciated. Thanks! Greg ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Business Software Galore! Free Excel Forum http://www.ozgrid.com/forum *** Hi ...

Adding a button with a function on protected sheets.
How do i add a button to 'reset/clear' the data on a worksheet that is protected and uses VLookup data (from another worksheet). Everyday this table will have data chosen from combo boxes or manually entered in allowable editable fields and at the end of the day after the files is saved, I need to clear that data for use on the next day. How is this Reset button applied with allowable edit ranges, VLookup data and a protected worksheet? Thanks There's several ways to do this. 1. Instead of straight vlookups, use =IF(ISBLANK(VLOOKUP(....)),"",VLOOKUP(....)) wher...

AutoPreview works intermittently
Outlook 2002 SP2 Affected Inbox is connecting to an IMAP server AutoPreview set to 'Preview unread items' I am an IT technitian and I have a user who likes to have her unread mail items in her Inbox to use the AutoPreview feature. At one point I went to View/Current View/Customize Current View.../Other settings... and chose 'No AutoPreview' option. I recently restored the 'Preview unread items' option. Now, an email will only AutoPreview if: I manually mark a read email as unread (right clicking on email and choosing 'Mark As Unread') or: right clic...

Autofilter on protected work book?
Autofilter works on a protected workbook, but not when it is a line in a macro, e.g.: Selection.AutoFilter Field:=1, Criteria1:="YES" It cause a macro error. This is true even if I enable 'Allow all user of the workbook to Use Autofilter'. I'm sure I've done this before and it worked, but not now. Does anyone know if this is a bug in 2003? Or a way around it? Or another way of selecting a number of rows by a single criterion (in a macro)? Good morning Muppet Does this article help? http://www.contextures.com/xlautofilter03.html#Protect HTH DominicB -- ...