Shade alternate filtered rows

Hi and welcome to the forum.

It's not clear from you picture if this is actually a "table".

If it is, then you can just choose one of the automatic formats and it will colour the alternate lines for you, even if you filter.

If it isn't already a table, select the first cell (Date in your example), then got to Insert and select Table. You will be asked to confirm the range and you should also check the box to say your table has headers. Once you've created the table, you will see a new tab on the ribbon for Table Tools. In the Table Styles, you can choose a format you like, including banded rows.

Does that help?

Just make your table a 'table object' and Excel will handle that properly - you won't need any conditional formatting.

Try this...

Data Range

	A	B	C
1	Region	Status	Term
2	West	No	3
3	East	No	1
4	West	Yes	1
5	South	No	2
6	East	No	1
7	South	No	1
8	North	Yes	4
9	North	No	3
10	West	No	2

Select the range A2:C10 starting from cell A2.

Conditional formatting

Formula: =MOD(SUBTOTAL(3,$A1:$A$2),2)=0

Apply the desired fill color

Now, if you filter Region on West you will get:

Data Range

	A	B	C
1	Region	Status	Term
2	West	No	3
4	West	Yes	1
10	West	No	2

You could use Tony's solution, but for general reference, to make a table you just highlight the area you are working with, then press ctrl+T

Check the box for 'headers' if you have them (otherwise it will make a generic header row for you).

Many members are unable to see images in the *.png format due to forum compatibility issues with some browsers.

If you need to post an image post it in the *.jpg format.

Even better than posting images... post a SMALL sample file. That way we can test solutions directly in the file with the relevant data.

Your data starts in cell A3 so the corrected formula would be:

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

Works for me when I test it (add new dates to column A then sort).

In case you still wanted to go down the table route (or just for future reference) I managed to make it into a table in your test file.

I think the problem was that you had changed some of the formatting so that overrides whichever table style you choose. I selected the whole table, then on the Home tab, click on the "Clear" drop down (at the right hand end of the ribbon under Editing), then chose Clear Formats. This gets rid of all the formatting so you can then go back to the Table Tools and pick a style. You'd also have to reformat the dates and currency values but at least it solves the banding problem.

I've attached an updated version just so you can see how it looks

You're welcome. Thanks for the feedback!

Hi all,

Thank you for all the suggestions. I just dug this one up while searching for the correct formula and wanted to pay my respects.

First of all, I am a big fan of tables and convert pretty much any collectoin of data in excel to a table... but on rare occasions a table is just not what you need (for example: if you need the headers to be dynamic using formulas) or my personal favorite: you have a table and you want to emphasize certain colunms but do not want to loose the dark/light color highlighting. This is where the Mod/Subtotal combination really comes in handy as the dark/light rows stay aligned with those of the table's own standard highlighting and stay aligned even if you have a filter applied.

Capture.JPG