Looking for (MAX(B4:B79) to include only black font.

[lgroulx2000]

Hi,

I have the following formula: =IF(B4>1,IF(MAX(B4:B79)-TODAY()>14,"No Work 14 Days?",MAX(B4:B79)),""). The problem is I don't want Max(B4:B79) to include cells with red or green font. I only want to include cells with font: xlAutomatic.

A VBA solution would be fine.

Any ideas?

Thanks,

Larry

 

[Domenic]

Try...

Sub MaxBlackFont()

Dim i As Long
Dim BlackFontCount As Long
Dim NextCell As Long
Dim MyArray() As Long

'Count the number of cells whose font is black
For i = 4 To 79
    If Cells(i, "B") <> "" And Cells(i, "B").Font.Color = 0 Then
    BlackFontCount = BlackFontCount + 1
    End If
Next i

ReDim MyArray(BlackFontCount - 1)

'For each cell whose font is black, assign it's value to the variable MyArray
NextCell = 0
For i = 4 To 79
    If Cells(i, "B") <> "" And Cells(i, "B").Font.Color = 0 Then
        MyArray(NextCell) = Cells(i, "B").Value
        NextCell = NextCell + 1
    End If
Next i

'The result is entered in E2
If Range("B4").Value > 1 Then
    If Application.Max(MyArray) - Date > 14 Then
        Range("E2").Value = "No Work14 days?"
    Else
        Range("E2").Value = Format(Application.Max(MyArray), "mmm d, yyyy")
    End If
Else
    Range("E2").Value = ""
End If
    
End Sub