Shading GROUPS of Similar Rows of Data in Filtered List

[kruss77]

I've looked and looked and can't seem to find the solution to my particular problem...

I'm trying to apply shading to rows of data that are all from the same calendar month. In an UNFILTERED range, all data grouped by month would be shaded alternatively by month (JAN, MAR, MAY, JUL, SEP, NOV all shaded. FEB, APR, JUN, AUG, OCT, DEC not shaded). That's the easy part which I've already got down. The problem is I also then want every other months' data group to appear alternatively shaded AFTER I filter the list. That is to say, if MAR is filtered from the visible list, I still want each months' group that's still visible be shaded alternatively so that no two months data group are shaded at the same time.

My head hurts, and I've seen some pretty amazing stuff from this group, so please help if you can!

I'm not a VB guy so I need a formula or layered formulas for the solution.

Thanks.

Ken

 

[Eric W]

Welcome to the forum.

I've assumed that your month (JAN, FEB, etc.) is in column A, and that it consists of the actual text JAN, FEB, etc. If it's just a date, then we can adjust the formula. I also assumed that you start in row 2, and that all months are sorted together, and if you filter out a month, the entire month is filtered out. Given that,

Select A2:L100 (the range of your table). Click Conditional Formatting > New Rule > Use a formula. Enter this formula:

=MOD(SUM((SUBTOTAL(103,OFFSET($A$1:$A$2,ROW($A$1:$A1)-ROW($A$1),0))=2)*($A$1:$A1<>$A$2:$A2)+(SUBTOTAL(103,OFFSET($A$1,ROW($A$1:$A1)-ROW($A$1),0))=0)*(SUBTOTAL(103,OFFSET($A$2,ROW($A$2:$A2)-ROW($A$2),0))=1)),2)

Click Format... and choose your fill color. Let us know if this works.

 

[kruss77]

YES, YES, YES!!!!!

Perfect!!!!!

It worked amazingly well! Perfect! Exactly what I wanted!

You folks NEVER fail to amaze me with what you can do!

Thank you so very much!!!!!

Ken

 

[kruss77]

BTW... when I filter (Autofilter) using other criteria, it screws up the shaded rows (no longer consistent by month). Any idea why?

Ken

 

[Eric W]

The basic algorithm for this is it counts the number of times above the current row where the value in Ax is different from Ax+1, OR there is one filtered row followed by a non-filtered row. Then rows with odd counts are highlighted, even rows aren't. So if you have multiple filtered sections within a month, that will mess up the count. I'm not sure offhand if there's a way to get around that, but I'll cogitate on it.

 

[kruss77]

Eric:

Thank you for the explanation. Sometimes it's good to know "why" something works (or doesn't) so that I can determine whether it's possible to use elsewhere in other applications I develop occasionally!

Thanks to experts like you my life is a lot easier!

Thank you again for your kind assistance!

Ken

 

[Eric W]

Here's one option. Select a column you're not using, I used L for this example. Again with your months in A, starting in row 2, put this formula in L2:

=ISNA(MATCH(A2&"1",A$1:A1&SUBTOTAL(103,OFFSET($A$1,ROW($A$1:$A1)-ROW($A$1),0)),0))*SUBTOTAL(103,A2)

confirm with Control+Shift+Enter. Drag down the column as needed. Remove the previous Conditional Formatting, select A2:G100 (your range), click Conditional Formatting > New Rule > Use a Formula and enter:

=MOD(SUM($L$2:$L2),2)

and select your fill color.

This one works by looking at the rows above the current row, and if we can't find a non-filtered row with the same month, we want to toggle the banding. You can now hide the L column if you want. Combining the formulas into a single CF formula will be tricky, it will involve 2D array processing, and will be long and probably inefficient. But I may play around a bit more. Let me know how this works.

 

[kruss77]

H O L Y C R A P D U D E !!!!!!!!!

WORKS LIKE A CHARM!!!!

Works PERFECTLY with ALL list sorting (regardless of column sort criteria)!!!!

Amazing!

You are the man!

Thank you SO VERY MUCH!!!!!

Ken

 

[Eric W]

Glad to help! That was interesting to work out.