Dave,

Thanks very much for taking the time to explain this
to me. I finally understand it.

Regards,
Rosemary
-----Original Message-----
Sometimes, it's easier to see what happens if you use
that conditional
formatting in a helper column of cells.

I added another column and plopped this into E2 (for me)
=MOD(SUBTOTAL(3,$A$2:$A2),2)

Then I dragged down to match the number of rows in the
data.

You'll notice that when nothing is filtered, you get:
1,0,1,0,1,0,1.... (alternating 1's/0's)

Now filter that list:
The visible cells in that helper column still look like
an alternating sequence
of 1,0,1,0,...

The =subtotal() function respects hidden cells (when
hidden by an autofilter).

And =subtotal(3,$a$2:$a2)
counts (the 3 portion)
the number of cells that have something in them from A2
to the row you're in.

Note that if you used a column that somes contained empty
cells (not formulas
that evaluated to ""), then you're formula will not work
the way you want.


wrote:

-----Original Message-----
Hi
try the following:
- row 1 is the heading row
- column A is the filtered column
- select row 2:x
- goto 'Format - Conditional Format'
- enter the following formula:
=MOD(SUBTOTAL(3,$A$2:$A2),2)
- choose a fomat

-----Original Message-----
In VBA, how would I change the interior colour of
alternate rows in a filtered list ie. visible rows
only?

Thanks,
Rosemary
.

.

Frank, thank you for your solution (3rd time lucky!). It
also works, though I don't really understand why. It
also
causes some of the hidden rows to get coloured too, in
what appears a very random pattern ie. when the list is
un-
filtered, I see that rows 2, 3, 7, 9, 10, 11 are
coloured, whereas when the list is filtered rows 2 7
and 9
are correctly coloured

Could you perhaps enlighten me as to how it works?
Thanks,
Rosemary

--

Dave Peterson

.