On VBA insert a module and on the module this code:
Function OverFour(Target As Range, top_value As Integer) As Integer
thisrow = Target.Row 'row of the order cell
thiscolumn = Target.Column 'column of the order cell
thisvalue = Target.value 'value of the order cell
thisorder = Cells(thisrow, thiscolumn)
notSame = True
newrow = thisrow
finalvalue = 0
'the following part checks how many rows are on the block of repeated values
While notSame = True
newrow = newrow + 1
k = Cells(newrow, thiscolumn)
j = Cells(newrow, thiscolumn - 1)
If k = 1 Or k = "" Then
newrow = newrow - 1
notSame = False
End If
Wend
'if the number of rows is larger than top_value
'and this cell is in the range from top_value to the end of the block
'then output an 1
If Cells(newrow, thiscolumn) >= top_value Then
If newrow - thisrow <= top_value - 1 Then
finalvalue = 1
End If
End If
OverFour = finalvalue
End Function
Now, on cell D2 insert:
=OverFour(B2,4)
And copy (drag down) to the other cells in column D.
The function uses two references:
- The cell of the order,
B2
in this case.
- The desired top value,
4
in this example.
The good thing about it is that it could work if you decide to mark the top 2, 3, 4, 5 or whatever you want.