colour alternate rows in filtered list

  • Thread starter Thread starter RW
  • Start date Start date
R

RW

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

Thanks,
Rosemary
 
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
 
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.
 
Dave,

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

Regards,
Rosemary
 
Back
Top