colour alternate rows in filtered list

R

RW

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

Thanks,
Rosemary
 
F

Frank Kabel

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
 
G

Guest

-----Original Message-----
Try this on Chip Pearsons site, it will give you all you need!

http://www.cpearson.com/excel.htm

You will find a section on colour formatting here.

Simon
Click to expand...

Simon,


Thanks for your reply. I couldn't actually find what
I wanted on the site you pointed me to (I'm sure it was
there but I am probably too impatient/jaded to weed it out
(been working too hard recently!)) but did find useful
help at:
http://j-walk.com/ss/excel/usertips/tip043.htm

For the benefit of anyone else who wants to do something
similar, a solution is:

With Range(???).SpecialCells(xlCellTypeVisible)
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=MOD(ROW(),2)=0"
.FormatConditions(1).Interior.ColorIndex = ???
End With

Regards,
Rosemary
 
G

Guest

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

.
Click to expand...
Frank,
 
G

Guest

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

.
Click to expand...
Frank
 
G

Guest

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

.
Click to expand...
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
 
D

Dave Peterson

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.
 
R

RW

Dave,

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

Regards,
Rosemary
 
Top