Count - specific consecutive order

[no need]

Hello,
I'm making an excel that represents a calendar.
- In columns, we have months from B to M (B2 being January and M2 being December).
- In rows, we have days (A3 is day 1 and A33 is day 31).
For each cell within B3:M33, we'll input either A or B.
January, March, May, July, August, October and December will all have inputs until row 33 included as they have 31 days/month
April, June, September and November will all have inputs until row 32 as they have 30 days/month
February will have inputs until row 30 as it will have 29 days in 2024.
I'm looking for a formula or a macro that does the following:
If the input "B" is consecutively entered for more than 89 times, then display this message "blabla". Else, display this "albalb".
The complexity 1 I face is that, I can't manage to find a formula that takes this specific order as the definition of consecutiveness.
For instance, after B33 (i.e day 31 of Jan), it's not B32 but C3 (i.e day 1 of Feb).
The complexity 2 I face is that, what I have tried counts across B3:M33 without taking the consecutive order into account (like a countif).
Anybody can help? So far, below my (chatgpt's) last trial:

Sub CheckConsecutivity()

Dim ws As Worksheet

Set ws = ThisWorkbook.Sheets("Planning") ' Change "Sheet1" to your sheet name



Dim cell As Range

Dim consecutiveCount As Integer

consecutiveCount = 0



' Specify the order of cells

Dim cellOrder As Variant

cellOrder = Array("B3", "B4", "B5", "B6", "B7", "B8", "B9", "B10", "B11", "B12", "B13", "B14", "B15", "B16", "B17", "B18", "B19", "B20", "B21", "B22", "B23", "B24", "B25", "B26", "B27", "B28", "B29", "B30", "B31", "B32", "B33", _

"C3", "C4", "C5", "C6", "C7", "C8", "C9", "C10", "C11", "C12", "C13", "C14", "C15", "C16", "C17", "C18", "C19", "C20", "C21", "C22", "C23", "C24", "C25", "C26", "C27", "C28", "C29", "C30", "C31", _

"D3", "D4", "D5", "D6", "D7", "D8", "D9", "D10", "D11", "D12", "D13", "D14", "D15", "D16", "D17", "D18", "D19", "D20", "D21", "D22", "D23", "D24", "D25", "D26", "D27", "D28", "D29", "D30", "D31", "D32", "D33", _

"E3", "E4", "E5", "E6", "E7", "E8", "E9", "E10", "E11", "E12", "E13", "E14", "E15", "E16", "E17", "E18", "E19", "E20", "E21", "E22", "E23", "E24", "E25", "E26", "E27", "E28", "E29", "E30", "E31", _

"F3", "F4", "F5", "F6", "F7", "F8", "F9", "F10", "F11", "F12", "F13", "F14", "F15", "F16", "F17", "F18", "F19", "F20", "F21", "F22", "F23", "F24", "F25", "F26", "F27", "F28", "F29", "F30", "F31", "F32", "F33", _

"G3", "G4", "G5", "G6", "G7", "G8", "G9", "G10", "G11", "G12", "G13", "G14", "G15", "G16", "G17", "G18", "G19", "G20", "G21", "G22", "G23", "G24", "G25", "G26", "G27", "G28", "G29", "G30", "G31", "G32", _

"H3", "H4", "H5", "H6", "H7", "H8", "H9", "H10", "H11", "H12", "H13", "H14", "H15", "H16", "H17", "H18", "H19", "H20", "H21", "H22", "H23", "H24", "H25", "H26", "H27", "H28", "H29", "H30", "H31", "H32", "H33", _

"I3", "I4", "I5", "I6", "I7", "I8", "I9", "I10", "I11", "I12", "I13", "I14", "I15", "I16", "I17", "I18", "I19", "I20", "I21", "I22", "I23", "I24", "I25", "I26", "I27", "I28", "I29", "I30", "I31", "I32", "I33", _

"J3", "J4", "J5", "J6", "J7", "J8", "J9", "J10", "J11", "J12", "J13", "J14", "J15", "J16", "J17", "J18", "J19", "J20", "J21", "J22", "J23", "J24", "J25", "J26", "J27", "J28", "J29", "J30", "J31", "J32", _

"K3", "K4", "K5", "K6", "K7", "K8", "K9", "K10", "K11", "K12", "K13", "K14", "K15", "K16", "K17", "K18", "K19", "K20", "K21", "K22", "K23", "K24", "K25", "K26", "K27", "K28", "K29", "K30", "K31", "K32", "K33", _

"L3", "L4", "L5", "L6", "L7", "L8", "L9", "L10", "L11", "L12", "L13", "L14", "L15", "L16", "L17", "L18", "L19", "L20", "L21", "L22", "L23", "L24", "L25", "L26", "L27", "L28", "L29", "L30", "L31", "L32", _

"M3", "M4", "M5", "M6", "M7", "M8", "M9", "M10", "M11", "M12", "M13", "M14", "M15", "M16", "M17", "M18", "M19", "M20", "M21", "M22", "M23", "M24", "M25", "M26", "M27", "M28", "M29", "M30", "M31", "M32", "M33")



For Each cellName In cellOrder

Set cell = ws.Range(cellName)

If cell.Value = "B" Then

consecutiveCount = consecutiveCount + 1

If consecutiveCount > 89 Then

ws.Range("O13").Value = "blabla"

ws.Range("O13").Interior.Color = RGB(255, 0, 0) ' Red color

Exit Sub

End If

Else

consecutiveCount = 0

End If

Next cellName



' If the loop completes without meeting the condition

ws.Range("O13").Value = "albalb"

ws.Range("O13").Interior.Color = RGB(0, 255, 0) ' Green color

End Sub

 

[felixstraube]

Here is a function that will work in excel 2019:
Result in B36

Libro1
	A	B	C	D	E	F	G	H	I	J	K	L	M
2		ene-24	feb-24	mar-24	abr-24	may-24	jun-24	jul-24	ago-24	sep-24	oct-24	nov-24	dic-24
3	1	A	B	B	B	B	A	B	B	B	A	A	A
4	2	A	B	B	B	A	A	B	A	B	B	A	B
5	3	A	B	B	B	A	B	A	B	B	B	A	A
6	4	A	B	B	B	A	B	A	A	B	B	A	A
7	5	A	B	B	B	B	B	A	B	A	A	A	A
8	6	A	B	B	B	A	B	A	B	B	A	A	A
9	7	A	B	B	B	A	A	B	A	A	B	A	A
10	8	B	B	B	A	A	B	B	A	B	A	A	B
11	9	B	B	B	B	A	A	B	B	A	A	B	B
12	10	B	B	B	B	A	B	A	A	A	B	A	B
13	11	B	B	B	B	A	B	A	A	B	A	B	B
14	12	B	B	B	A	B	B	B	A	A	A	A	A
15	13	B	B	B	B	B	A	B	B	A	B	A	B
16	14	B	B	B	A	B	A	B	A	A	B	B	B
17	15	B	B	B	B	A	B	A	A	A	B	B	A
18	16	B	B	B	B	A	B	B	B	B	A	A	A
19	17	B	B	B	A	A	B	A	B	B	A	B	A
20	18	B	B	B	A	B	A	A	A	A	B	A	B
21	19	B	B	B	A	A	A	B	A	A	A	A	B
22	20	B	B	B	A	B	A	B	B	A	A	A	B
23	21	B	B	B	B	A	B	A	B	A	B	B	B
24	22	B	B	B	B	B	A	B	A	B	A	A	B
25	23	B	B	B	A	A	A	A	B	B	A	B	B
26	24	B	B	B	B	B	A	A	A	B	A	A	A
27	25	B	B	B	A	B	B	A	B	A	A	A	A
28	26	B	B	B	B	B	A	A	A	B	B	B	B
29	27	B	B	B	A	A	A	A	B	A	B	B	B
30	28	B	B	B	A	B	A	B	B	B	A	A	A
31	29	B	B	B	A	A	A	A	A	A	A	B	B
32	30	B		B	B	A	A	B	A	A	B	B	B
33	31	B		B		B		B	B		A		B
34
35
36		blabla
Hoja1

Cell Formulas
Range	Formula
B36	B36	=IF(IFERROR(SEARCH("BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB",CONCAT(B3:B33,C3:C33,D3:D33,E3:E33,F3:F33,G3:G33,H3:H33,I3:I33,J3:J33,K3:K33,L3:L33,M3:M33)),0),"blabla","albalb")

 

[felixstraube]

A slightly cleaner solution:

=SI(SI.ERROR(HALLAR(CONCAT(SI(FILA(INDIRECTO("A1:A90"));"B";""));CONCAT(B3:B33;C3:C33;D3:D33;E3:E33;F3:F33;G3:G33;H3:H33;I3:I33;J3:J33;K3:K33;L3:L33;M3:M33));0);"blabla";"albalb")

 

[Fluff]

Another option

=IF(ISNUMBER(SEARCH(REPT("B",90),CONCAT(TRANSPOSE(B3:M33)))),"blabla","albalb")

 

[felixstraube]

That formula is much cleaner!

 

[no need]

Here is a function that will work in excel 2019:
Result in B36

Libro1
	A	B	C	D	E	F	G	H	I	J	K	L	M
2		ene-24	feb-24	mar-24	abr-24	may-24	jun-24	jul-24	ago-24	sep-24	oct-24	nov-24	dic-24
3	1	A	B	B	B	B	A	B	B	B	A	A	A
4	2	A	B	B	B	A	A	B	A	B	B	A	B
5	3	A	B	B	B	A	B	A	B	B	B	A	A
6	4	A	B	B	B	A	B	A	A	B	B	A	A
7	5	A	B	B	B	B	B	A	B	A	A	A	A
8	6	A	B	B	B	A	B	A	B	B	A	A	A
9	7	A	B	B	B	A	A	B	A	A	B	A	A
10	8	B	B	B	A	A	B	B	A	B	A	A	B
11	9	B	B	B	B	A	A	B	B	A	A	B	B
12	10	B	B	B	B	A	B	A	A	A	B	A	B
13	11	B	B	B	B	A	B	A	A	B	A	B	B
14	12	B	B	B	A	B	B	B	A	A	A	A	A
15	13	B	B	B	B	B	A	B	B	A	B	A	B
16	14	B	B	B	A	B	A	B	A	A	B	B	B
17	15	B	B	B	B	A	B	A	A	A	B	B	A
18	16	B	B	B	B	A	B	B	B	B	A	A	A
19	17	B	B	B	A	A	B	A	B	B	A	B	A
20	18	B	B	B	A	B	A	A	A	A	B	A	B
21	19	B	B	B	A	A	A	B	A	A	A	A	B
22	20	B	B	B	A	B	A	B	B	A	A	A	B
23	21	B	B	B	B	A	B	A	B	A	B	B	B
24	22	B	B	B	B	B	A	B	A	B	A	A	B
25	23	B	B	B	A	A	A	A	B	B	A	B	B
26	24	B	B	B	B	B	A	A	A	B	A	A	A
27	25	B	B	B	A	B	B	A	B	A	A	A	A
28	26	B	B	B	B	B	A	A	A	B	B	B	B
29	27	B	B	B	A	A	A	A	B	A	B	B	B
30	28	B	B	B	A	B	A	B	B	B	A	A	A
31	29	B	B	B	A	A	A	A	A	A	A	B	B
32	30	B		B	B	A	A	B	A	A	B	B	B
33	31	B		B		B		B	B		A		B
34
35
36		blabla
Hoja1

Cell Formulas
Range	Formula
B36	B36	=IF(IFERROR(SEARCH("BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB",CONCAT(B3:B33,C3:C33,D3:D33,E3:E33,F3:F33,G3:G33,H3:H33,I3:I33,J3:J33,K3:K33,L3:L33,M3:M33)),0),"blabla","albalb")

Thank you so much, I have tested and it workd perfectly. The only thing is that this wouldn't work with longer words as there would be too many characters for the formula to work. Yet, creative and working solution.

 

[Peter_SSs]

The only thing is that this wouldn't work with longer words as there would be too many characters for the formula to work.

They would need to be very long words wouldn't they?
Did you have something in mind? I think the suggestion could likely be adapted if either CONCAT or REPT was likely to exceed its character limit.