filtering colours

[robert_woodie]

Is it possible to filter a coloumn of data depending on the backgroun
colour of the cell?

I hope so!!

Rober

 

[DNF Karran]

Not directly:

If you put this in a module:
Function GetColourIndex(rngCell As Range)

GetColourIndex = rngCell.Interior.ColorIndex

End Function

then put a helper column:

=GetColourIndex(A1)

to find the number of the colour and filter from there

Dunca

 

[Andy B]

Hi

Have a look here:
http://www.cpearson.com/excel/colors.htm
There's loads of good stuff on Chip's site.

 

[robert_woodie]

GetColourIndex = rngCell.Interior.ColorIndex

the code above from duncan works but not if the colour has been altere
by conditional formatting....is this possible?

Thank

 

[DNF Karran]

f you follow the links through on Chip's site you'll find a functio
that may help. There may however be an easier way-

Add a helper column again and put:

if(A1=1,1,0)

where A1 is a cell with a conditional format of "equal to" 1.

THis will enable you to pick up the cells easily.

Dunca

 

[robert_woodie]

ok thanks for your help so far i just cant quite make it work!1

This is the formula i am using in the conditional format:
=AND(SEARCH($C$3,$C13),ROW()>5,$C$3<>"")

So i put:
=IF(AND(SEARCH($C$3,$C13),ROW()>5,$C$3<>"",C13<>""),1,0)

I cant seem to make it work...am i right in thinking it should say 1 i
its found zero if not?

thanks
rober

 

[DNF Karran]

The formulas inside the AND() function should be logical tests and i
they are all true then AND() will return a true value for the IF(
function.

I think the problem may be the SEARCH($C$3,$C13) as this is not
logical test it is returning a value. You would need:

=IF(AND(SEARCH($C$3,$C13)="ABC",ROW()>5,$C$3<>"",C13<>""),1,0)

to make the search a logical test.

You are right that 1 will be displayed if true, zero if false.

Dunca

 

[DNF Karran]

Almost right- the search returns the position (a number) where a tex
string is found not a string. You would therefore need:

=IF(AND(SEARCH($C$3,$C13)=1,ROW()>5,$C$3<>"",C13<>""),1,0

 

[robert_woodie]

this is not critical....it will just make it look good! but wen it i
false it returns #Value instead of zero. however it does return 1 whe
meant to which is all i really need to filter it.

thanks
Robert

 

[DNF Karran]

if thats the problem then:

IF(ISERR(IF(AND(SEARCH($C$3,$C13),ROW()>5,$C$3<>"",C13<>""),1,0)),1,0)

I'm sure this could be "refined" but I'm not 100% clear on what you ar
doing. But if it works why worry!!

Dunca