filter by background color

[MikeL]

I am using XL 2000. My question is:
How do I filter by background color?

For example, if the background of cells A1 and A3 are colored Yellow and Cells A2, A4 and A5 are blank, is there a way to filter the yellow cells? Or vice versa the blanks?

I have used a color sort macro to get all the same colors adjacent to each other, but I am looking to do this using a Filter.

Thanks in advance,
MikeL

 

[phantom1975]

I don't think there is a filter that will do what you want. The only alternative I can suggest is to hide the unwanted rows in your macro. This will produce the same sort of result a filter would. You would then have to unhide the rows when you want to see all the data again.

 

[Legacy 12630]

Provided that the colours are not the result of conditional formatting, it could be done by adding a column to contain a formula that returns the colour codes and then filtering by that column :-

1. Go to Insert>Names>Define.
2. In the NamesInWorkbook box type Clr
3. In the RefersTo box type =GET.CELL(38,INDIRECT("rc[1]",FALSE))
4. Click OK
5. Insert a column immediately before the column with the colours
6. In the first cell of the inserted column type =Clr
7. Fill down as far as required. This returns the colour codes.
8. Filter by the inserted column.

NB :
Do not copy/paste to a different worksheet any cells that contain the formula =Clr , since it will cause XL to crash.

 

[Barry Katcher]

Here ya' go, Mike. Trick the little ******; use a Custom AutoFilter with the same criteria you used for your Conditional Formatting:

Book1
	A	B	C	D	E	F	G
1
2		This,
3		Unfiltered
4		Customer	Date	Weight	ShiptoState		ConditionalFormat
5		Barry	1/1/2003	2500	NJ		ofcellsB5:B8
6		Larry	1/5/2003	4500	NY		Formulais:
7		Harry	1/7/2003	3000	CA		=OR($E5="NY",$E5="CA")
8		Moe	1/7/2003	6000	NJ		(Tohi-liteallNewYork&Calif.)
9
10		Results					Data/Filter/AutoFilterof
11		inthis,					columnE(ShiptoState)
12		Filtered					setto"Custom",
13		Customer	Date	Weight	ShiptoState		Condition1:equalsNY
14		Larry	1/5/2003	4500	NY		or
15		Harry	1/7/2003	3000	CA		Condition2:equalsCA
16
Sheet3