still searching for color text

[Guest]

Went to the processing colour website as instructed. The formula I need is
=SUMPRODUCT(--(ColorIndex(A2:A28,TRUE)=3))
However, it didn't work. I got *Name in the cell instead of the necessary
number.

I'm trying to search through a list of names (of which some are in blue) and
get it to return the number of names that are in blue. Can you help.

Thanks!

 

[Peo Sjoblom]

You have to install the UDF (user defined function), it is not built in,
that is why you were given the color link, copy the code from colorindex and
follow
instructions from here

http://www.mvps.org/dmcritchie/excel/install.htm

 

[JulieD]

Hi

haven't seen any of your previous posts so would like to know what the web
address of the "processing colour website" to check the formula and
instructions as i think there needs to be a colorindex function added. if
you'ld like to post the web address this will help.

Cheers
JulieD

 

[Bob Phillips]

There is a function called ColorIndex at that website, you need to copy that
to your workbook as described.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Here are a few alternatives:

=SUMPRODUCT(--(CellColor(A2:A28)=3))


VB: Alt+F11 > Insert > Module: (Copy and paste the below)

Public Function CellColor(myCell As Range) As Variant
Application.Volatile True
CellColor = myCell.Interior.ColorIndex
End Function

Public Function FontColor(myCell As Range) As Variant
FontColor = myCell.Font.ColorIndex
End Function

Public Function NumberFormat(Cell As Range) As String
NumberFormat = Cell.NumberFormat
End Function

Public Function FontBold(myCell As Range) As Variant
FontBold = myCell.Font.Bold
End Function

Have fun
Ola Sandstrom


Note:
You can either use Application.Volatile True or NOW()*0.
Application.Volatile True will make your spreadsheet recalculate everything
--> Excel runs slower.
=FontColor(A17)+NOW()*0 --> faster but messy formulas. In your example that
would be =SUMPRODUCT(--((FontColor(A2:A28)+NOW()*0)=3))

http://www.excelforum.com/showthread.php?t=333258&highlight=cellcolor