Filter by Color

E

Eileen Li

Our office spreadsheets are very colorful (different
color means different things). Is there an easy way I can
filter specific column by color? For example, filter by
green or yellow.

Thanks
 
C

CLR

I have another column that has codes in it that correspond to the colors in
the column I want to sort.......sorting on the code column effectively gives
me a sort on the colored column.....

Vaya con Dios,
Chuck, CABGx3
 
J

JON-JON

Hello,

When you say color is it the font or the cell interior? Is the color due to
conditional formatting or you color it manually?
You could give other clearer idea if you will be more specific.

Regards,

Jon-jon
 
J

JON-JON

Hello Eileen,

I don't think that you could use xl to filter by color b'coz filter only
recognize value but not format. One possible solution is to get a value for
that color that xl could identify. Give this a try

Press Alt+F11. VB editor will appear. Go to Insert|Module then paste
this UDF.

Function ColorInterior(Mycolor As Range) As String
ColorInterior = Mycolor.Interior.ColorIndex ' Get value of interior
color
End Function

Function ColorFont(Mycolor As Range) As String
Application.Volatile
ColorFont = Mycolor.Font.ColorIndex ' Get value of font color
End Function

Now return to xl then right this formula to your helper column

Assume that your cell is in A1

=ColorInterior(A1)
or
=ColorFont(A1)

You now have a value for your color that can be the criteria for your
filter. However, changing format does not trigger calculation this means
that when you change the color the calculation will not change
automatically. You have to "re-write" the formula (Press F2 then enter).
Another caveat, this only works for color that you put manually and not as
result of a conditional formatting.

Regards,

Jon-jon
 
J

JON-JON

Error,

Delete line :Application.Volatile '

JON-JON said:
Hello Eileen,

I don't think that you could use xl to filter by color b'coz filter only
recognize value but not format. One possible solution is to get a value for
that color that xl could identify. Give this a try

Press Alt+F11. VB editor will appear. Go to Insert|Module then paste
this UDF.

Function ColorInterior(Mycolor As Range) As String
ColorInterior = Mycolor.Interior.ColorIndex ' Get value of interior
color
End Function

Function ColorFont(Mycolor As Range) As String
Application.Volatile
ColorFont = Mycolor.Font.ColorIndex ' Get value of font color
End Function

Now return to xl then right this formula to your helper column

Assume that your cell is in A1

=ColorInterior(A1)
or
=ColorFont(A1)

You now have a value for your color that can be the criteria for your
filter. However, changing format does not trigger calculation this means
that when you change the color the calculation will not change
automatically. You have to "re-write" the formula (Press F2 then enter).
Another caveat, this only works for color that you put manually and not as
result of a conditional formatting.

Regards,

Jon-jon


due
Click to expand...
 
D

Dave Peterson

Actually, these are two of those user defined formulas where you want
"application.volatile".

When you change the font color or fill color, it's not enough of a change to
excel to cause a recalculation. So the function won't reevaluate.

If you have application.volatile in your code, then the _next_ time excel
calculates, the values will be updated.

If you don't have that application.volatile in your code, you'll have to make a
change (even F2|Enter is enough) to the the cell containing the formula or the
cell the formula uses.

(And that means you'll want to hit F9 to force a recalc before you trust the
results of the formula, too.)
 
Top