Filtering cells, then using fill/color??

I’ve noticed this happens about 50% of the time I use the Filter option within Excel (I’m using Vista)...

I need to filter thousands of rows of text by the "contains" option for three cities (London, Rome, Madrid). I need to do this 3x because each city needs to be highlighted/filled in a different color.

I come across problems after I have completed the first filter, eg London and filled the cells with Yellow. I then go on to the next Filter for "Rome" and try to fill them in Orange, but Excel will not let me use the fill option again.

I do just want to make it clear that I am not filtering by color - I am adding color AFTER I have filtered by city.

I am quite a savvy Excel user, so if you have any advanced help here, I would be gratefully appreciative. As I say, this happens about 50% of the time I use Excel, sometimes it works, sometimes it doesn't...

Many thanks in advance

Can you use conditional formatting on all of your cells to automatically format the cells depending on what city is located in it? That would seem to be the easiest solution if Vista and/or Excel is giving you trouble.

ChemistB

Why not just use conditional formatting to automatically add the colors?
To answer your question, though, I think hitting Alt+; after highlighting the cells (but before trying to add the color) will solve your problem. What that does is select only visible cells.

For various reasons the conditional formatting will take longer than necessary because I have various other filters on other columns too.

However, DarkYam, I played around with some controls, and found that hitting "select visible cells" before filling with color seems to work just fine. I guess Excel wasn't sure if I wanted the hidden or the visible cells colored in.

Thanks!