If Formula Based on Font Color

[gsmith923]

I need an IF formula to return a value if the font in the cell is red and a separate formula for the same to return the value if in green.

For example: for cells Q2:T3 I need an if formula to return the value in the cell that is red (=if(Q2:T3="red font", cell value with red font,""). There would only be 1 cell with red font, and there may be not be any cells with red font. I would need this formula to cover 25 dual rows: Q2:T3, Q4:T5 continuing down to Q42:T43.

I would need the same to identify a value in green font in the same grouping of cells noted above.

The same would then be needed for V2:Y3 all the way down to V42:Y43 and lastly for AA2:AD3 all the way down to AA42:AD43.

I am thinking VBA may be needed and I have very little experience with VBA. Any help would be greatly appreciated!

 

[mikerickson]

Two things,
1) How is the color applied?
Detecting the color of a cell that has Conditional Formatting is a big challange. Its easier to test if the condition has been met than it is to determine what color the cell is. (When looking for red, how do you want cell formats that display negatives in red handled.

2)If the color is applied by the user changing the font color, my thought is that that is a lousy way to store data.
The reason that associating color with meaning is a bad idea comes out when I ask "what is the difference between a cell being red and a cell being green"? Excel allows words to be put on a computer screen. If one has to ask "what does that color mean?", then words should have been used in conjunction with colors.

While #2 is a short version of my "color should not be data" rant, the answer to #1 will help folks craft a UDF to meet your needs.

 

[gsmith923]

All the data on this spreadsheet is a manual entry. Just no way around it. The red and green fonts will be dates and they will signify closing / opening events while there will be other entries that will not be a closing (red font date) or opening (green font date). The results of this formula I seek will help me to automate another workbook.

Thanks

 

[mikerickson]

This UDF will return the contents of the first cell in the aRange that has the font color set to findColorIndex.

=FindCellOfColor(Q2:T3, 3) will return the value of the first cell in Q2:T3 that has a red font color. If no cell in the range has a red font #N/A is returned. (change that line if you want a different result in that error case.)

The user needs to remember one thing. Changing a cell's color does not trigger calculation. If the user changes a cell's color, but not its value, they need to press F9 for the function to update itself. (That's paragraph 5 in the full version of the "color is for highlights" rant.)

Function FindCellOfColor(aRange As Range, findColorIndex As Long) As Variant
    Application.Volatile
    Dim oneCell As Range
    
    If findColorIndex < 1 Or findColorIndex > 56 Then findColorIndex = xlAutomatic
    
    With aRange
        Set aRange = Application.Intersect(.Parent.UsedRange, .Cells)
    End With
    
    FindCellOfColor = CVErr(xlErrNA):Rem nothing found value
    If Not aRange Is Nothing Then
        For Each oneCell In aRange
            If oneCell.Font.ColorIndex = findColorIndex Then
                FindCellOfColor = oneCell.Value
            End If
        Next oneCell
    End If
End Function