Highlight the max number of consecutive positive numbers

[excel_1]

Trying to Highlight the max number of consecutive positive numbers.



with the help of the following formula, can able to get the max number of consecutive positive numbers. but when tried to be applied as a conditional formula - no luck.

{=MAX(FREQUENCY(IF(A2:A20>0,ROW(A2:A20)),IF(A2:A20<=0,ROW(A2:A20))))}

 

[Eric W]

Welcome to the MrExcel forum!

Try:

Book1
	A	B	C
1	Number list	Number list	5
2	109	109
3	34	34
4	-221	-221
5	-561	-561
6	281	281
7	3120	3120
8	710	710
9	45	45
10	32	32
11	-12	-12
12	80	80
13	56	56
14	-231	-231
15	-4671	-4671
16	-60	-60
17	-16	-16
18	-42	-42
19	-71	-71
20	55	55
21
Sheet7

Cell Formulas
Range	Formula
C1	C1	=MAX(FREQUENCY(IF(A2:A20>0,ROW(A2:A20)),IF(A2:A20<=0,ROW(A2:A20))))
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B:B	Expression	=AGGREGATE(14,6,COUNTIF(OFFSET(B1,ROW(INDIRECT("1:"&C$1))-C$1,0,C$1),">0"),1)=C$1	text	NO
A:A	Expression	=MAX(COUNTIF(OFFSET(A1,SEQUENCE(C$1,,0,-1),0,C$1),">0"))=C$1	text	NO

The A column uses SEQUENCE, available in Excel 365. The B column uses AGGREGATE, available in Excel 2010 and later.

 

[excel_1]

wow, that's awesome. no words but indeed it's an immense help.

 

[excel_1]

just curious, if the same thing can be achieved via VBA.

 

[Eric W]

Sure, here's one way:

Sub HiLiteStreak()
Dim MaxStreak As Long, MyData As Variant, i As Long, j As Long

    Range("A:A").Interior.Color = xlNone
    MaxStreak = Evaluate("=MAX(FREQUENCY(IF(A2:A20>0,ROW(A2:A20)),IF(A2:A20<=0,ROW(A2:A20))))")
    
    MyData = Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row).Value
    For i = 1 To UBound(MyData) - MaxStreak + 1
        For j = i To i + MaxStreak - 1
            If MyData(j, 1) <= 0 Then Exit For
        Next j
        If j >= i + MaxStreak Then
            Cells(i + 1, "A").Resize(MaxStreak).Interior.Color = vbCyan
            Exit Sub
        End If
        i = j
    Next i
    
End Sub

 

[excel_1]

Thank you again

 

[Peter_SSs]

I don't know if it is possible with your data but if there is more than one section with equal maximum positive streak then Eric's code will only highlight the first one. Removing the 'Exit Sub' line though should allow for all maximum consecutive groups to be highlighted.

I also note that one part of Eric's code is only looking at A2:A20 but another part is looking for wherever the data ends in column A. That may also need tidying up.

.. or here is another possible approach to the highlighting.
(I have assumed at least 1 positive value in column A)

Sub HiLiteStreak_v2()
  Dim MaxStreak As Long
  Dim rA As Range
  
  Application.ScreenUpdating = False
  With Range("A2", Range("A" & Rows.Count).End(xlUp))
    .Interior.Color = xlNone
    MaxStreak = Evaluate(Replace("=MAX(FREQUENCY(IF(#>0,ROW(#)),IF(#<=0,ROW(#))))", "#", .Address))
    .Offset(-1).Resize(.Rows.Count + 1).AutoFilter Field:=1, Criteria1:=">0"
    For Each rA In .SpecialCells(xlVisible).Areas
      If rA.Rows.Count = MaxStreak Then rA.Interior.Color = vbCyan
    Next rA
    .Parent.AutoFilterMode = False
  End With
  Application.ScreenUpdating = True
End Sub