Excel Guru Needed...

JonB

JonB

JonB

Associate
Joined
3 Oct 2006
Posts
2,304
Location
London
Hello,

I can't find anyone in my office who knows how to solve this problem. Hopefully someone on here will be able too :-)

The problem is this. I have a lot of data that I want to be able filter. I'd also like the row colours to alternate. However when I filter the data, this happens.





Does anyone know how to keep the cell formatting seperate to the cell contents, if that makes sense?

Many thanks for your help,

Jon
 
Ok, I've written a macro to do this. How would I get it to automaticall run when a user filters the data?

Thanks.

Jon
 
Conditional formatting for the win!

Select the cells in the list ie A2:Z29.
Choose Format|Conditional Formatting
From the first dropdown, choose Formula Is
For the formula, enter =MOD(SUBTOTAL(3,$A$1:$A2),2)
Click the Format button.
On the Patterns tab, select a colour for shading
Click OK, click OK
Filter the list, and the shading will alternate in the visible rows.

Simon
 
Oh thanks muchly. Much easier than having to use macros.

Anychance you could explain the formula bit to me?

Thanks

Jon
 
Well the subtotal bit uses function 3 (countA) to count every cell with a value in it.
Subtotal doesn't 'see' rows hidden by the filter command so they get ignored.

The $A$1 fixes the start of the count to the first cell, you could change it to $A$2 if you want the first line of the results to be highlighted. $A2 makes sure it stays counting only column A but as conditional formatting applies the formular to each row, the 2 part can't be fixed.

The mod function just divides the number returned by the subtotal command by 2. If it's 0, ie an even number then the conditional formatting sees it as false so leaves the row alone.
Anything not divisible by 2 will get 1 remainder and conditional formatting will see it as true.

Simon
 
Last edited:
Ah brilliant, thanks.

Just as a side note, does excel produce a Worksheet_Change event when you filter data? Or is there a filter event at all?

Jon
 
Any recomendations for general Excel Books. Ones which cover 'bits of everything'? Thanks
 
You must log in or register to reply here.
Back
Top Bottom