Color every other cell after a filter.

**pacer0928** · 09-08-2009, 11:59 PM

Hello everyone, I am currently using conditional formating to color every other row of my spread sheet. Unfortuantley, when I utilize a FILTER, to filter the data fields, some of the rows are gone, and often, two even rows remain so they are the same color.

For example:

There is data fields...

1 - A
2 - B
3 - A
4 - B
5 - A
6 - A
7 - A
8 - A
9 - A
10 - A

If I filter for B, my spreadsheet will only display rows 2 & 4, both of which are the same color due to my conditional formatting (odd rows = one color, even rows = another). I am trying to make it so that these filtered cells would be of different colors, like my spreadsheet before any filters are applied.

I was hoping that somebody on the board could help me arrive to a solution regarding this problem. This problem only arises in FILTERING scenarios as SORTING does not remove any cells, merely reorganizes them, and my conditional formatting works fine.

Thank you very much for your time.

**shg** · 09-09-2009, 12:05 AM

If Row 1 is headers, then in some column (say, A) set the CF formula to

=MOD(SUBTOTAL(3, A$1:A2),2)=1

and format as you like.

**pacer0928** · 09-09-2009, 12:12 AM

Hello, basically row 1-6 is all the headers/decoration and from row 7 onward, i have my entire data set. From here onward I wish to color my cells. By the way, I have about 20 columns , each of which are capable of being filtered. Multiple filters may be applied at the same time which may effect the cell row ordering.

I am looking for a dynamic formula that always works, so it would work before I filter data (and in doing so, screw up the order of the cells as some cells are now hidden) and after I filter the data.

After changing your equation to row 7 in A, it still does not work. I don't know what is wrong. Thanks.