Filter by colour

[VegasNath]

Hello,
The 'Filter by colour' option in xl2007 is a great addition, but the more you get, the more you want! As far as I can see, you can only filter to one colour. I would like to filter certain colours in and certain colours out. Are there any tips or tricks that I am missing? Or any useful vba to achieve such a task?

TIA

[HansV]

Try the ASAP Utilities (free for personal use).

[VegasNath]

Thanks Hans. I use ASAP Utils on my home pc, though I had never noticed that ability.

However, my need for this is at work where AU is not an option. I only need this in one workbook (lots of sheets) so I was hoping for a double-click type event.

I have a macro that shows a userform which details a. visible worksheets and b. hidden worksheets, left and right buttons to move the sheets between visible and hidden. My curiosity is whether a similar type of UF could be used to ascertain all used colours in the column where the d/c occurs, offering the ability to hide/unhide the rows according to the fill colour.

Would you say this is achievable, or ??
Thanks

[HansV]

It's not really clear to me what you have and what you want, could you provide more detailed information?

[VegasNath]

I don't have anything concrete as yet, but am wondering if the concept of the 'show/hide sheets' macro which uses a userform, could be used to show/hide rows based on the fill colour.

I have many identical sheets, all with the same data layout, with headers, and autofilter active. What I would like to be able to do is:
1. Double click header cell to show userform
2. Userform details all colours in use within that column, including 'No Fill' (somehow??). A maximum of 8 colours would be in use.
3. User ability to select which rows should be visible / hidden, based on colours.
4. OK button hides userform and hides / unhides rows based on above selections.

Any assistance that you can offer would be great. Thanks

[HansV]

Why use colours? It's much easier to use text or number values! You can use data validation to limit what the user can enter.

If you really must use colours, you can use Chip Pearson's Color Functions In Excel to fill a column with the color index of the cell, and use these numbers for filtering etc.

[VegasNath]

Thanks, i'll take a look.