Applying alternate color to rows while auto-filtering

[Robb D]

I have a sheet with multiple columns (for the sake of example, A through H), and have the auto-filter applied. I currently have every other row shaded (in all columns), and I want to maintain this after filtering on column B. I tried this solution from another thread on just column B (as a test) in conditional formatting:

Formula is=EVEN((SUBTOTAL(3,$B$1:B1)))=SUBTOTAL(3,$B$1:B1)

but it doesn't seem to work; all rows in column B immediately turn to the fill color I specified in Format, and remain the same color after applying the autofilter. Am I missing something in the formula?

 

[Peter_SSs]

Robb D

Try this (assumes there is data in each row of column A but could be adapted if this is not the case):

1. Select A1:H??
2. Format|Conditional Formatting|Formuala is..|=MOD(SUBTOTAL(3,$A$1:$A1),2)=0 and choose your format colour

Now try applying different AutoFilter conditions.

 

[Robb D]

Thanks, Peter -
unfortunately that doesn't seem to work; when I filter on column B, column A-H all turn to the formatted color, not just the alternate rows. There is data in every row in column A for the range I'm selecting.

 

[Peter_SSs]

Thanks, Peter -
unfortunately that doesn't seem to work; when I filter on column B, column A-H all turn to the formatted color, not just the alternate rows. There is data in every row in column A for the range I'm selecting.

Strange, it is working for me. Can you try this?

1. Select a cell in your range in column A, go into Format|Conditional Formattting, select and copy the formula (Ctrl+C) and paste it back on this board together with the cell address it came from (eg A5).
2. Confirm that in Conditional Formatting, you are only using 1 condition.
3. Tell us which version of Excel you are using

 

[Robb D]

1. Select a cell in your range in column A, go into Format|Conditional Formattting, select and copy the formula (Ctrl+C) and paste it back on this board together with the cell address it came from (eg A5).

I think I'm misunderstanding something; you're saying I should apply the formula you gave me

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

into Conditional Formatting; then copy that same formula back out again, and post it here?

One thing I just tried - I filtered on column A instead of column B, and the formatting works fine - but the goal is to filter on column B, and have columns A through H colored alternately. Does this only work if you filter on the leftmost column? Perhaps what I need to do is swap the column data, which is not a problem.

2

. Confirm that in Conditional Formatting, you are only using 1 condition.

Confirmed.

3. Tell us which version of Excel you are using

Excel 2000 (9.02720)

Thanks again for the help, apologies if I'm being obtuse about the problem...

 

[Peter_SSs]

Robb D

No you weren't misunderstanding. In the past on this board sometimes suggestions made to problems have been rejected as no good when they clearly work for the person suggesting the solution, ...and sometimes the problem is that the suggested solution has not been applied exactly as instructed. I was just trying to ensure that wasn't the problem here - hope you are not offended by my checking.

Again just double-checking:
1. Confirm that the formuls you quoted

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

in fact does not have the ( ) around it when used in Conditional Formatting?

2. Confirm that the above quoted formula is from the Conditional Formatting from a cell in row 1 only and that a cell in row 3 has
=MOD(SUBTOTAL(3,$A$1:$A3),2)=0

3. Confirm that it is alternate ROWS to be shaded, not alternate columns. This question asked because of this from your last post

the goal is to filter on column B, and have columns A through H colored alternately

I am using Excel 2003 but as far as I know the suggestion should also work in Excel 2000. I confirm that when I filter on column B (or any other column) the visible rows of columns A to H are coloured alternately.

I am running out of ideas as to why yours is not working. One thing that may be worth trying is to start a new sheet in a new workbook, put some data in manually (not formulas) and apply the suggested formatting again. If this works correctly, it might at least point the finger at your aother workbook or worksheet rather than the version of Excel or anything else.

Good luck!

 

[Robb D]

Peter -
thanks for the help. I figured out the problem - it was my brain.

In my sheet I'm actually applying this formatting on rows 3 through 150, as opposed to 1 through 150, so that's why it wasn't working correctly. I changed the formula to refer to the correct rows, and voila! Alternately shaded rows both before and after the filtering.

Sorry to make that harder than it needed to be - I appreciate the assistance.

 

[Peter_SSs]

Ahh.. persistence triumphs again!