This UDF will find the longest run of <0 numbers in a column.

Function countmax(MyLetter As String, myRange _
As Range) As Integer
Dim c As Range, TempMax As Integer, _
fReset As Boolean
For Each c In myRange.Cells
If c.Value < 0 Then
TempMax = TempMax + 1
Else
TempMax = 0
End If
countmax = Application.WorksheetFunction _
.Max(countmax, TempMax)
Next
End Function

Usage is........=countmax("<0",A1:A100)

To count =0 just change the line "If c.Value < 0 Then" to

"If c.Value = 0 Then"

Change formula to =countmax("=0",A1:A100)


Gord Dibben MS Excel MVP

On Tue, 9 Oct 2007 11:44:01 -0700, dlbeiler
wrote:

I have a column of random numbers that are conditionally formatted for the
font to be blue if the number is = 0 and red if <0. How do I find the
longest run of numbers that are = than 0 and the longest run that are < 0
within the column.