red flag

[khalil]

hello


how to red flag when 9 consecutive entries are the same in column A, there are some blank cells in between.
we can use A1 as fixed cell to show red, and the entries will be in A2,A3....A100.

thanks

 

[Trevor G]

This code will count how many entries match A1 based on range A2:A100 it returns a message saying how many it has found.

Perhaps you can work on this

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> count1()<br><SPAN style="color:#00007F">Dim</SPAN> cell <SPAN style="color:#00007F">As</SPAN> Range<br><SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br><SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> cell <SPAN style="color:#00007F">In</SPAN> Range("A2:A100")<br><SPAN style="color:#00007F">If</SPAN> cell.Value = Range("A1") <SPAN style="color:#00007F">Then</SPAN><br>i = i + 1<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">Next</SPAN><br>MsgBox "Next Count " & i<br><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[khalil]

thanks, but it didn't work am i missing something

 

[Trevor G]

Try this then.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> count1()<br><SPAN style="color:#00007F">Dim</SPAN> cell <SPAN style="color:#00007F">As</SPAN> Range<br><SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br><SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> cell <SPAN style="color:#00007F">In</SPAN> Range("A2:A100")<br><SPAN style="color:#00007F">If</SPAN> cell.Value = Range("A1") <SPAN style="color:#00007F">Then</SPAN><br>i = i + 1<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">Next</SPAN><br><br><SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> cell <SPAN style="color:#00007F">In</SPAN> Range("A2:A100")<br><SPAN style="color:#00007F">If</SPAN> cell.Value = Range("A1") <SPAN style="color:#00007F">Then</SPAN><br>cell.Interior.Color = vbRed<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">Next</SPAN><br>MsgBox Range("A1") & " Found this many times " & i<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[khalil]

thank you man for helping me ,

do i need to fill any thing in the code

any way what i am trying to do

if i find the word solo 9 times in a range of cells in column A2:A100 without any other word in between, may be blank cells, red flag will appear in cell A1

then if the word dual appears , then i start counting again the word solo for another 9 consecutive times, & so on

i am new in these kind of codes
if you walk me through it , it will be highly appreciated,


thanks

 

[ZVI]

This function returns max length of chain in series with skipping of empty values:

' Max length of chain in series with skipping of empty values
' Cell formula: =MaxLengh(A2:A100)
' VBA call    :  MsgBox MaxLengh(Range("A2:A100"))
Function MaxLengh(Series) As Long
  Dim arr, old, v$, x, y&
  arr = Series
  y = 1
  For Each x In arr
    v = Trim(x)
    If Len(v) > 0 Then
      If StrComp(v, old, vbTextCompare) = 0 Then
        y = y + 1
        If y > MaxLengh Then MaxLengh = y
      Else
        y = 1
        old = x
      End If
    End If
  Next
End Function

Sub Test_MaxLengh()
  MsgBox MaxLengh(Range("A2:A100"))
End Sub

That function is for True/False flag if defined chain's length Level in series is achieved:

' Flag = chain length in series is >= Level, empty values are skipped
' Cell formula: =MaxLenghFlag(A2:A100,9)
' VBA call    :  MsgBox MaxLengh(Range("A2:A100"),9)
Function MaxLenghFlag(Series, Level) As Boolean
  Dim arr, old, v$, x, y&, z&
  arr = Series
  y = 1
  For Each x In arr
    v = Trim(x)
    If Len(v) > 0 Then
      If StrComp(v, old, vbTextCompare) = 0 Then
        y = y + 1
        If y > z Then z = y
        If z >= Level Then MaxLenghFlag = True: Exit For
      Else
        y = 1
        old = x
      End If
    End If
  Next
End Function

Sub Test_MaxLenghFlag()
  MsgBox MaxLenghFlag(Range("A2:A100"), 9)
End Sub

 

[khalil]

thanks
i tried to use it , it didn't work,
could you please give a hint

i am still new in vba , i have some basics


thanks

 

[ZVI]

1. Macro should be allowed in Excel
2. Copy into clipboard code of the 1nd part from post #6 (with MaxLengh function)
3. Being in sheet press Alt-F11, VBE is created
4. Add Module1 from VBE menu: Tools – Insert – Module
5. Paste code into added module
6. Copy into clipboard code of the 2nd part from post #6 (with MaxLenghFlag function)
7. Paste code into Module1 (refer to p.5)
8. Press Alt-Q to quit VBE and to return into sheet
9. Put formula in A1 cell: =MaxLenghFlag(A2:A100,9) to get True/False result
10 . You can also put formula into B1 cell: =MaxLengh(A2:A100) to count max length in series.

Data layout and formulas:

Excel Workbook
	A	B	C
1	TRUE	9	FALSE
2
3	word
4	text
5	solo	9 in series
6
7	solo
8	solo
9	solo
10	solo
11	SOLO
12	Solo
13
14	solo
15	solo
16	word
17	text
18
Sheet

 

[khalil]

thank you very much
you are a good man

 

[ZVI]

thank you very much
you are a good man

Glad it helped, Khalil