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

lgroulx2000

Board Regular
Joined
Jan 6, 2009
Messages
100
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
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,406
Try...

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,842
Messages
5,598,396
Members
414,235
Latest member
sucosama

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top