find excel rows with a background color

[Guest]

I frequently get large spreadsheets (1000's of rows) where a few rows here
and there will be highlighted by changing the background color to something
other than white. Is there a way to filter the rows on background color,
analogous to the way rows can be filtered by cell contents?

 

[Guest]

hi,
background color is formating. the filter tool filters on hard data only so
no.... you can't filter with color.
But you're not the first person to want this. Sorting by color may be an
option. unfortunately excel don't have a tool for this either.
fortunately programmers have developed a tool..... see this site.

http://www.cpearson.com/excel/sortbycolor.htm

regards
FSt1

 

[Gord Dibben]

Lou

Which version of Excel?

In 2003 you can specify the Format color to look for under
Edit>Find>Options>Format>Format.

You could try this alternate method which doesn't use VBA.

Select the color from the Patterns dialog and Find All.

In the found dialog box the first cell will be highlighted. Hold SHIFT key and
scroll down to bottom of dialog box and select last cell.

Edit>Copy and paste to a new sheet.


Gord Dibben MS Excel MVP

 

[Guest]

Hi Gord,

That works great - it's one of those "obvious" solutions that I never would
have thought of on my own.

Thanks

Lou

 

[Guest]

Hi FSt1 -

Thanks - this lets a program read these cells and select relevant rows by
color. Terrific.

Lou