Re: Alternate row colouring that also works when sheet is filtered?

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@xxxxxxxxxxxxxx wrote:

On Mar 17, 3:02 pm, Dave Peterson <peter...@xxxxxxxxxxxxxxxx> 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
.

Relevant Pages

  • Re: Alternate row colouring that also works when sheet is filtered?
    ... Close conditional formatting. ... A2 to B30 start out solid light yellow colour. ... row turns to alternate colouring. ... I can remember coming across in Excel. ...
    (microsoft.public.excel)
  • Re: Format Conditions Formula1
    ... Excel 2007 that the formula you see when you edit a rule is for the first ... works fine for all the cells. ... conditional formatting but now I am having trouble in Excel 2007. ... created a VB Macro that would evaluate each row and if there was no ...
    (microsoft.public.excel.programming)
  • RE: Protect a worksheet but allow cell editing
    ... I am new to Excel VBA, ... I have a worksheet where I want the user to be able to edit cells (delete, ... expression for conditional formatting is on the user data sheet because Excel ... I'm either getting the ability to unhide columns and edit data, ...
    (microsoft.public.excel.programming)
  • Re: Text disappears when word wrap is used
    ... Make sure that there is not Conditional Formatting in Effect ... I trying to specifically look for Excel 2003. ... > are no merged cells on the sheet. ... >> Hi Mark, ...
    (microsoft.public.excel.misc)
  • Re: Find "invisible" conditional formatting
    ... recently shared public files. ... black on Excel 2000. ... I can't simply select all cells and delete the conditional formatting as ...
    (microsoft.public.excel.programming)