find excel rows with a background color
 

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?

find excel rows with a background color
 

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

"Lou" wrote:

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?

find excel rows with a background color
 

Lou

Which version of Excel?

In 2003 you can specify the Format color to look for under
EditFindOptionsFormatFormat.

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.

EditCopy and paste to a new sheet.


Gord Dibben MS Excel MVP

On Wed, 23 May 2007 07:32:01 -0700, Lou wrote:

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?

find excel rows with a background color
 

Hi Gord,

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

Thanks

Lou

"Gord Dibben" wrote:

Lou

Which version of Excel?

In 2003 you can specify the Format color to look for under
EditFindOptionsFormatFormat.

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.

EditCopy and paste to a new sheet.


Gord Dibben MS Excel MVP

On Wed, 23 May 2007 07:32:01 -0700, Lou wrote:

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?

find excel rows with a background color
 

Hi FSt1 -

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

Lou

"FSt1" wrote:

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

"Lou" wrote:

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?