Using Font Color in a Funtion Criteria
Posted by Karenlee on March 16, 2001 10:46 AM
Using a function how do I search a specifict array of cells for a font color? Basically I want this: if IF(B6=(font color=red),"J","L"). Is there an easy way to search for a specific font color?
Posted by Ian on March 16, 2001 2:05 PM
Lame Suggestion
I don't know how to do exactly what you are asking, but are the different color set up by conditional formatting? If so, you could use an array formula to total them based on the same conditions?
Posted by Dave Hawley on March 16, 2001 2:25 PM
?
Hi Karenlee
You would need a Custom Function for this, so here is one!
To use it Push Alt+F11 and go to Insert>Module and paste in this function:
Function FontClrFind(FontColor As Range, SearchRange As Range)
Dim clr As Integer, cell As Range, Found As Boolean
Application.Volatile
clr = FontColor.Font.ColorIndex
Set SearchRange = SearchRange.SpecialCells(xlCellTypeConstants)
Found = False
For Each cell In SearchRange
If cell.Font.ColorIndex = clr Then
Found = True
Exit For
End If
Next cell
FontClrFind = Found
End Function
Push Alt+Q to return to Excel and save.
Now push Shift+F3 and scroll down to "User Defined Functions" and select "FontClrFind" and click OK. To use your example you would use it like this:
=IF(FontClrFind(C1,B6),"J","L")
Where C1 contains a Font with the Color red.
B6 is then cell to check.
You can also use it to search a range of cells like:
=IF(FontClrFind(C1,B6:B200),"J","L")
If any cells font within the range B6:B200 is red the result will be "J" as the result would be true.
This type of formula will ONLY recalculate when Excel recalculates OR a cell Content within the range changes. The change of a font color within the range is not considered a change.
Dave
OzGrid Business Applications
Posted by Ian on March 16, 2001 2:35 PM
Good Suggestion
Posted by Celia on March 16, 2001 5:44 PM
Re: Lame Suggestion
If the colors are the result of conditional formatting, this is not a lame suggestion - it is a very good suggestion. The formula structure would be =IF(B6=[conditional format criteria],"J","L")
Celia
Posted by Ivan Moala on March 17, 2001 1:21 AM
Re: Lame Suggestion
Celia is right, this is not a lame suggestion as
marks UDF will not work on conditionally formated
cells.
Ivan
Posted by Ivan Moala on March 17, 2001 1:22 AM
Re: Lame Suggestion
Sorry that was Daves UDF.....
Posted by Mark W. on March 19, 2001 8:57 AM
Re: Lame Suggestion
Yeah, it wasn't me. As a general rule I discourage
the use of color in worksheet logic. Color gradations
can be subtle, people are color blind, a different font
color can be applied to each character in a cell,
and powerful tools such as PivotTables don't
recognize color when used as a data attribute.
