filtering colours

R

robert_woodie

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

I hope so!!

Rober
 
D

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
 
R

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
 
D

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
 
R

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
 
D

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
 
D

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
 
R

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
 
D

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
 
Top