Filtering coloured cells

rollo · May 21st, 2002, 08:32 AM

I have a macro which colours the cells in two columns of a worksheet according to the text that the cell may contain. For example, if a cell contains "BSR" it will appear red, "APG" will appear blue, "PTR" will appear yellow, "KML" will appear green - and so on. All other cells remain white.
Here's the question ..... is there any way that I can now filter out all of the rows containing these coloured cells (and retain the row 1 column headings) - in much the same way an autofilter would work?
Any ideas would be greatly appreciated - many thanks.

sann0638 · May 21st, 2002, 08:35 AM

can you not just filter by the text, if the text corresponds directly to a colour?

rollo · May 21st, 2002, 08:47 AM

Thanks for the suggestion, Mike ..... but the problem is there are as many as 10 different text entries that I would like to filter out simultaneously.

Ricky Morris · May 21st, 2002, 09:11 AM

Add a new column with the following formula:

=IF(or(A1="A",A1="B",A1="C",....),"Y","N")

where A,B,C... are the conditions you want to filter on. Then you can use auto filter on the column you've just added for "Y"

rollo · May 21st, 2002, 09:16 AM

Thanks for the help, Ricky. Have managed to resolve the problem by using an advanced filter first, then running the macro to colour the cells.
Thanks again.
rollo

May 21st, 2002, 08:35 AM	#2
sann0638 Board Regular Join Date: Mar 2002 Posts: 175	can you not just filter by the text, if the text corresponds directly to a colour? __________________ Regards, Mike.

May 21st, 2002, 09:11 AM	#4
Ricky Morris Board Regular Join Date: Mar 2002 Posts: 363	Add a new column with the following formula: =IF(or(A1="A",A1="B",A1="C",....),"Y","N") where A,B,C... are the conditions you want to filter on. Then you can use auto filter on the column you've just added for "Y" __________________ It's never too late to learn something new. Ricky

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 21st, 2002, 08:32 AM	#1
rollo New Member Join Date: Feb 2002 Posts: 43	I have a macro which colours the cells in two columns of a worksheet according to the text that the cell may contain. For example, if a cell contains "BSR" it will appear red, "APG" will appear blue, "PTR" will appear yellow, "KML" will appear green - and so on. All other cells remain white. Here's the question ..... is there any way that I can now filter out all of the rows containing these coloured cells (and retain the row 1 column headings) - in much the same way an autofilter would work? Any ideas would be greatly appreciated - many thanks.

May 21st, 2002, 08:47 AM	#3
rollo New Member Join Date: Feb 2002 Posts: 43	Thanks for the suggestion, Mike ..... but the problem is there are as many as 10 different text entries that I would like to filter out simultaneously.

May 21st, 2002, 09:16 AM	#5
rollo New Member Join Date: Feb 2002 Posts: 43	Thanks for the help, Ricky. Have managed to resolve the problem by using an advanced filter first, then running the macro to colour the cells. Thanks again. rollo