Conditional formatting for Row Banding doesn't work after Autofilter

**oOarthurOo** · 09-08-2009, 04:21 PM

For obvious reasons, the conditional formatting to shade alternate rows doesn't work when filtered. So I think I need another way of doing it. Luckily, my table is fairly static, rows aren't added or removed. The first column is excluded from the banding. As is the first and last row with data (1 & 67) respectively.

I found a relevant thread here, but the code is beyond my understanding.

**Ron Coderre** · 09-08-2009, 05:12 PM

Since you don't have an exceptionally large list, try this:

With B1:D67 containing your data
AND
assuming that Col_B will always contain a value (no blanks)

THEN

Please Login or Register  to view this content.

After AutoFiltering, the visible cells will display banding.

Is that something you can work with?

**oOarthurOo** · 09-09-2009, 03:09 PM

Wow. Outstanding Ron, thank you!

Trying to understand your formula here...

MOD(number,divisor): Returns the remainder after number is divided by divisor. The result has the same sign as divisor.

SUBTOTAL(function_num, ref1, ref2, ...): Function_num is the number 1 to 11 (includes hidden values) or 101 to 111 (ignores hidden values) that specifies which function to use in calculating subtotals within a list.

1 101 AVERAGE
2 102 COUNT
3 103 COUNTA
4 104 MAX
5 105 MIN
6 106 PRODUCT
7 107 STDEV
8 108 STDEVP
9 109 SUM
10 110 VAR
11 111 VARP

Ref1, ref2, are 1 to 29 ranges or references for which you want the subtotal.

COUNTA(value1,value2,...): Counts the number of cells that are not empty and the values within the list of arguments. Use COUNTA to count the number of cells that contain data in a range or array.

So, if I try and break down your formula..

=MOD(SUBTOTAL(3,$B$2:$B2),2)
You use subtotal because each filtered list is like a grouped subset of the larger dataset. Maybe autototal would automatically include the data hidden by the autofilter? In which case, I'd write 103 instead of 3?

I'm trying to understand how $B$2:$B2 works. When I create a new sheet, and fill it with 10 items in row 2 through 11 and write =counta($A$2:$A2) it only returns one item. Or if I write =subtotal(3,$A$2:$A2) it again returns one. But if it really did return one, then what would be returned is...

MOD = 1 / 2
MOD = 0.5

Then conditional formatting using this number... somehow?

In any event, if you don't have time to explain it to me, thank you very much for the voodoo

**Ron Coderre** · 09-09-2009, 03:35 PM

Regarding: =MOD(SUBTOTAL(3,$B$2:$B2),2)

• The SUBTOTAL function only calculates on the visible cells of an
autofiltered list. If no autofilters are engaged, it behaves the same as the
SUM function. When the first argument of the function is 3, it counts
non-blank cells. If you plan to hide rows by any other means
(grouping, row hiding, autofilter, advanced filter, etc), use 103 for
the first SUBTOTAL argument, instead of 3.

• The MOD function performs grade school division and returns the
remainder.
Example: =MOD(7,5) returns: 2
7 divided by 5 equals 1...with 2 remaining.

• The formula calculates the count of visible non-blank cells, divides that
count by 2 and returns the remainder, which will always be a 1 or a zero.

• Since conditional formatting requires a TRUE/FALSE formula, zeros are
evaluated to FALSE, ones are evaluated to TRUE.

• The initial formula is only referencing cell $B$2:$B2.
As it is copied down, it references more cells...
$B$2:$B3
$B$2:$B4
$B$2:$B5
etc.

Consequently, if Row_3 is hidden by the autofilter, the formula in B5
returns a count of 3 (cells B2, B4, and B5)

I hope that helps.