Exel 97 question how to recognize fill & font color

[steve case]

Well, I'm back at the well:

Cells in column A might have a fill color "yellow" or a font color "red" I'm looking for a formula to put in columns B and C =if(A1="fontcolorRed",1,"") and =if(A1="fillcolorYellow",1,"")

I'm sure there's syntax to accomplish what I want to do, I just don't know what it is, and help isn't much help.

 

[Iridium]

You could return the colour index - have a look at this page for the'Returning The ColorIndex Of A Cell' function

http://www.cpearson.com/excel/Colors.htm

For info the following macro (by Barry Katcher) will list the colorindex numbers with their corresponding colours - run on a blank w/s

Public Sub GetColours()
Dim x As Integer

Application.ScreenUpdating = False
For x = 1 To 56
    Cells(x, 1) = x
    Cells(x, 2).Interior.ColorIndex = x
Next x
Application.ScreenUpdating = True

End Sub

 

[Caliche]

I think you must use VBA. Take a look at www.ozgrid.com Excel and VBA newsletter August 2003 (Sum cells by background color, font color or another type of format). This article could give you a starting point.

HTH

 

[Greg Truby]

This looked fun and (relatively) quick: Created a UDF in VBA. 1st arg is range to check, 2nd is check "Font" or "Fill", 3rd is what color to test for. If you pass a number, checks colorindex property. If you pass a string, checks to see if that string corresponds to a VB colorconstant.

(Edit) Just saw the other posts and looked at Chip's site. Probably need an Application.Volatile in the UDF. (End Edit)

WS example:

color tester.xls
	A	B	C	D	E
1	TestFor	Fill	Fill	Font	Font
2	TestColor	Yellow	3	Red	4
3	one	TRUE	FALSE	FALSE	FALSE
4	two	FALSE	FALSE	TRUE	FALSE
5	three	FALSE	TRUE	FALSE	FALSE
6	four	TRUE	FALSE	FALSE	TRUE
7	five	FALSE	FALSE	TRUE	FALSE
Sheet1

UDF Code:<font face=Courier New><SPAN style="color:#00007F">Function</SPAN> TestForColor(rngCell<SPAN style="color:#00007F">As</SPAN> Range, _
                      strTestWhat<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">String</SPAN>, _
                      varTestColor<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Variant</SPAN>)<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Boolean</SPAN>
                      <SPAN style="color:#007F00">' If varTestColor is a number, assume testing for known colorindex else</SPAN><SPAN style="color:#007F00">' if string use color & colorconstants</SPAN>

    TestForColor =<SPAN style="color:#00007F">False</SPAN>            <SPAN style="color:#007F00">' default return value</SPAN>

    <SPAN style="color:#00007F">Dim</SPAN> lngTestColor<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Long</SPAN>
    strTestWhat = UCase(strTestWhat)
    <SPAN style="color:#00007F">If</SPAN> strTestWhat<> "FONT" _
    And strTestWhat<> "FILL"<SPAN style="color:#00007F">Then</SPAN><SPAN style="color:#00007F">Exit</SPAN><SPAN style="color:#00007F">Function</SPAN>
    <SPAN style="color:#00007F">If</SPAN> IsNumeric(varTestColor)<SPAN style="color:#00007F">Then</SPAN>
        <SPAN style="color:#00007F">If</SPAN> strTestWhat = "FONT"<SPAN style="color:#00007F">Then</SPAN>
            TestForColor = rngCell.Font.ColorIndex = varTestColor
        <SPAN style="color:#00007F">Else</SPAN>
            TestForColor = rngCell.Interior.ColorIndex = varTestColor
        <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">Else</SPAN>
        varTestColor = UCase(varTestColor)
        <SPAN style="color:#00007F">Select</SPAN><SPAN style="color:#00007F">Case</SPAN> varTestColor
            <SPAN style="color:#00007F">Case</SPAN> "BLACK"
                lngTestColor = vbBlack
            <SPAN style="color:#00007F">Case</SPAN> "BLUE"
                lngTestColor = vbBlue
            <SPAN style="color:#00007F">Case</SPAN> "CYAN"
                lngTestColor = vbCyan
            <SPAN style="color:#00007F">Case</SPAN> "GREEN"
                lngTestColor = vbGreen
            <SPAN style="color:#00007F">Case</SPAN> "MAGENTA"
                lngTestColor = vbMagenta
            <SPAN style="color:#00007F">Case</SPAN> "RED"
                lngTestColor = vbRed
            <SPAN style="color:#00007F">Case</SPAN> "WHITE"
                lngTestColor = vbWhite
            <SPAN style="color:#00007F">Case</SPAN> "YELLOW"
                lngTestColor = vbYellow
            <SPAN style="color:#00007F">Case</SPAN><SPAN style="color:#00007F">Else</SPAN>
                <SPAN style="color:#00007F">Exit</SPAN><SPAN style="color:#00007F">Function</SPAN>
        <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Select</SPAN>
        <SPAN style="color:#00007F">If</SPAN> strTestWhat = "FONT"<SPAN style="color:#00007F">Then</SPAN>
            TestForColor = rngCell.Font.Color = lngTestColor
        <SPAN style="color:#00007F">Else</SPAN>
            TestForColor = rngCell.Interior.Color = lngTestColor
        <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Function</SPAN></FONT>

HTH

 

[steve case]

Thank you all for the help, I was surprised that it really can't be done the way I thought!

The lesson I learned, is; "Don't flag your cells with color codes!"