Excel Text Color Filter 2

[NewfieGolfer]

Hi,

I was wondering if there is a way to filter a row by the color of the text contained in each cell. I think if there was a way to determine what the cell text color is using a formula then it would be no problem, just use the criteria in the advanced filter. But, is there a way to find out what color the text is using a formula. That is, can I return a numerical value for the text color.

Thanks,
NG

 

[SkipVought]

Hi,

Nope!

But this function will. Cut 'n' Paste into a Module. Use just like any other spreadsheet function...

Function TextColorIndex(rng)
  With rng
    If .Count > 1 Then Exit Function
    TextColorIndex = .Font.ColorIndex
  End With
End Function

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[xlbo]

nope - not without VBA

Your best bet is to add a column and to create a formula that mimics your reasoning for colouring the cells - then filter on that column

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[NewfieGolfer]

The above Function works great, but it doesn't refresh if I change the color of the text in the cell. Is there a refresh command I can put in this function?

Thanks,
NG

 

[SkipVought]

Function TextColorIndex(rng)
  Application.Volatile
  With rng
    If .Count > 1 Then Exit Function
    TextColorIndex = .Font.ColorIndex
  End With
End Function

Volatile MAY help, but the biggest condition is a recalculation of the sheet. Merely changing a font color is connected to no detectable event.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[xlbo]

Skip - will that work - changing the colour of the text won't cause a recalc so App.Volatile shouldn't make a difference....

You may be able to do something with one of the GET.CELL functions - there is one that returns the colour of the 1st character in a cell - more info on the MSKB - I'd be careful if you do persue this route though as it is no longer technically supported by MS - you can only use it because they've left it in there for backwards compatability

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[Suggie]

Skip,

I am trying to use the above code to find the colour of a cell, not the colour of the text. But I can't even get the code to work and find the colour of the text.

It obviously works so it must be me (again)

I have cut and paste it into a module and it's not working...how do I get it to run?

Thanks in advance,
Mark

 

[xlbo]

after pasting into a module, enter this into a cell on the spreadsheet
=TextColorIndex(A1)

where you want to test A1

Please see notes about recalculation however

To get the BACKGROUND colour of the cell, amend

TextColorIndex = .Font.ColorIndex

to

TextColorIndex = .INTERIOR.ColorIndex

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[Suggie]

Geoff,

Thanks.

This works by giving the number associated with that colour.

What I would like to do is within a spreadhsheet I have data in columns from A to J with data underneath to, say 200 rows.

Coulmns A, C and D will have cells that are colour coded.

I would like to filter on this colours.

Is the only way to add a column and use the above VBA code?

TIA
Mark

 

[xlbo]

yup - there is NO standard functionality to determine ANYTHING by colour. Your best bet is to include a formula that replicates the logic used to colour the cells and filter on that instead.....hmmmm....deja vu - that's exactly what I've already stated at the top of this thread

Rgds, Geoff

Yesterday it worked. Today it is not working. Windows is like that.

Please read FAQ222-2244 before you ask a question

 

[Suggie]

I know, I know...thanks.

Geoff, when I put in a Function into a module I don't seem to have it to be active in every workbook.

I know how to save a standard macro as an add-in, how do I get to save a 'function' macro as an add-in.

Again, thanks for your help.

 

[xlbo]

Same way as far as I know
Either that or put it in your PERSONAL.xls file

Rgds, Geoff

Yesterday it worked. Today it is not working. Windows is like that.

Please read FAQ222-2244 before you ask a question

 

[Suggie]

Thanks again.

Being a bit cheeky here, but if you time could look at the post I have in called 'Deleting Duplicate Rows with Advance Filter' that would really help...no-one seems to have an answere yet, or it's probaby the way I've worded it!