Filtering by color

[bucci35]

Is thier a way to filter by either cell color or font color?
Thanks

 

[Richard Schollar]

Hi

Not directly - you'd need either a macro to do it, or a helper column with a suitable UDF to give you the colorindex value of the target cell(s) so that you could then use a regulkar autofilter.

A suitable UDF can be found here:

http://www.mrexcel.com/board2/viewtopic.php?p=1126783#1126783

Post back if you need help utilising this.

Best regards

Richard

 

[bucci35]

what do I do with this udf? looks like some kind of vba code or something. I'm not too advanced in this stuff.

thanks,

 

[DominicB]

Good evening bucci35

You could try downloading and installing Ron de Bruin's free EasyFilter add-in - all the hard bits have already been done for you!

http://www.rondebruin.nl/easyfilter.htm

HTH

DominicB

 

[bucci35]

This works great, but now that I installed this how can I incorporate this to work inside of a macro?

 

[DominicB]

Hi bucci35

You can't, but that wasn't your question. Refer you back to Richard's post.

DominicB

 

[bucci35]

Ok,
can I filter by border? or by bold font?

 

[bucci35]

with a macro?

 

[DominicB]

Hi bucci35

Enter this code into a blank module within your project (Alt+F11, Insert > Module) :

Function CellColour(UsrRng As Range) As Integer
Application.Volatile True
CellColour = UsrRng(1, 1).Interior.ColorIndex
End Function

Lets say the range you want to filter is A1:E100, and the coloured cells are in column A. In cell F1 put the formula : =CellColour(A1) and copy it down.

This will return the ColorIndex value of each cell in column A. You can now record your macro, but filter by column F (not A), but the end result will have the same efect as if you'd filtered by colour in column A.

Next time you post remember to be more explicit about what you want - you'll get an answer quicker, and better tailored to what you want.

HTH

DominicB