Highlight consecutive(sequential) numbers in excel

[Samgraphics]

Hi, can someone please help me? I'm trying to highlight combinations of numbers if they increase incrementally, example 1,2,3,4,5 or combination where the interval is increasing at the same rate like 5,12,20,29,39 or combinations with equal intervals like 2,4,6,8,10. So any combinations like the ones I highlighted below would be highlighted.

Thank you in advance

All combinations 2023-24.xlsm
	C	D	E	F	G
1	1	2	3	4	5
2	1	2	3	4	6
3	1	2	3	4	7
4	1	2	3	4	8
5	1	2	3	4	9
6	2	4	6	8	10
DA LUO TO

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C1	Expression	=IF(AND(C1=D1-1, D1=E1-1, E1=F1-1, F1=G1-1), "Consecutive", "Not Consecutive")	text	NO

 

[Samgraphics]

Please be careful with your 'quotes' and only quote relevant posts or parts of them. I have just removed three quotes from your last post that, as far as I can see, had nothing to do with this follow-up question.


Could you give some sample data and expected results with XL2BB so that we can see what range(s) you are working with and how you would want the results presented? Make sure that there are some examples where there are 3 (or more?) consecutive numbers and some examples where there are no sets of 3 consecutive numbers.

Hi, thank you for your reply. Here is an example,

All combinations 2023-24.xlsm
	J	K	L	M	N
20	7	19	26	27	28
21	6	18	23	27	32
22	2	14	16	28	30
23	1	7	9	25	29
24	5	11	12	13	33
25	2	8	16	31	34
26	4	8	10	14	21
27	6	7	8	25	33
DA LUO TO

 

[Peter_SSs]

Thanks for the samples. Try this

23 12 17.xlsm
	J	K	L	M	N
20	7	19	26	27	28
21	6	18	23	27	32
22	2	14	16	28	30
23	1	7	9	25	29
24	5	11	12	13	33
25	2	8	16	31	34
26	4	8	10	14	21
27	6	7	8	25	33
3 consecutive

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J20:N27	Expression	=FIND("|1|1|","|"&$K20-$J20&"|"&$L20-$K20&"|"&$M20-$L20&"|"&$N20-$M20&"|")	text	NO

 

[Samgraphics]

Thanks for the samples. Try this

23 12 17.xlsm
	J	K	L	M	N
20	7	19	26	27	28
21	6	18	23	27	32
22	2	14	16	28	30
23	1	7	9	25	29
24	5	11	12	13	33
25	2	8	16	31	34
26	4	8	10	14	21
27	6	7	8	25	33
3 consecutive

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J20:N27	Expression	=FIND("|1|1|","|"&$K20-$J20&"|"&$L20-$K20&"|"&$M20-$L20&"|"&$N20-$M20&"|")	text	NO

Thank you very much, it's working the way I want, but it's a bit overworking. What I mean is it's also highlighting a little bit outside the scope of what I want. It's highlighting combinations with more than 3 consecutive numbers and you already helped me with a formula to do that. Is there a way to make it highlight exclusively combinations that have only three consecutive numbers? for example, if a combination has 4 or more consecutive numbers I'd like it to exclude them. For example, it will highlight 1,2,3,410 and I don't want that. I'll show you what I mean.

All combinations 2023-24.xlsm
	C	D	E	F	G
1	1	2	3	4	5
2	1	2	3	4	6
3	1	2	3	4	7
4	1	2	3	4	8
5	1	2	3	4	9
6	1	2	3	4	10
7	1	2	3	4	11
8	1	2	3	4	12
9	1	2	3	4	13
10	1	2	3	4	14
11	1	2	3	4	15
12	1	2	3	4	16
DA LUO TO

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C1:G98280	Expression	=COUNTIFS($J$1:$J$98280,$C1,$K$1:$K$98280,$D1,$L$1:$L$98280,$E1,$M$1:$M$98280,$F1,$N$1:$N$98280,$G1)	text	NO
C1:G98280	Expression	=MIN($D1:$G1-$C1:$F1)=MAX($D1:$G1-$C1:$F1)	text	NO
C1:G98280	Expression	=MIN(($E1:$G1-$D1:$F1)-($D1:$F1-$C1:$E1))=MAX(($E1:$G1-$D1:$F1)-($D1:$F1-$C1:$E1))	text	NO
C1:G324632	Expression	=FIND("|1|1|","|"&$D1-$C1&"|"&$E1-$D1&"|"&$F1-$E1&"|"&$G1-$F1&"|")	text	NO

All combinations 2023-24.xlsm
	C	D	E	F	G
46240	1	26	30	34	35
46241	1	26	31	32	33
46242	1	26	31	32	34
46243	1	26	31	32	35
46244	1	26	31	33	34
46245	1	26	31	33	35
46246	1	26	31	34	35
46247	1	26	32	33	34
46248	1	26	32	33	35
46249	1	26	32	34	35
46250	1	26	33	34	35
46251	1	27	28	29	30
46252	1	27	28	29	31
46253	1	27	28	29	32
46254	1	27	28	29	33
46255	1	27	28	29	34
46256	1	27	28	29	35
46257	1	27	28	30	31
46258	1	27	28	30	32
DA LUO TO

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C1:G98280	Expression	=COUNTIFS($J$1:$J$98280,$C1,$K$1:$K$98280,$D1,$L$1:$L$98280,$E1,$M$1:$M$98280,$F1,$N$1:$N$98280,$G1)	text	NO
C1:G98280	Expression	=MIN($D1:$G1-$C1:$F1)=MAX($D1:$G1-$C1:$F1)	text	NO
C1:G98280	Expression	=MIN(($E1:$G1-$D1:$F1)-($D1:$F1-$C1:$E1))=MAX(($E1:$G1-$D1:$F1)-($D1:$F1-$C1:$E1))	text	NO
C1:G324632	Expression	=FIND("|1|1|","|"&$D1-$C1&"|"&$E1-$D1&"|"&$F1-$E1&"|"&$G1-$F1&"|")	text	NO

Thank you so much. Also, if you wouldn't mind, explain how this formula works. I saw this "|" and I noticed it removes the border from the cells.
Thank you so much for your help and time.

 

[Peter_SSs]

It's highlighting combinations with more than 3 consecutive numbers and you already helped me with a formula to do that.

Not exactly. What I helped with was if all 5 numbers were consecutive (or all 5 had an equal gap). My previous suggestions would not highlight if 4 of the numbers were consecutive but not 5.

What I mean is it's also highlighting a little bit outside the scope of what I want.

Make sure that there are some examples where there are 3 (or more?) consecutive numbers and some examples where there are no sets of 3 consecutive numbers.

Perhaps now then you see why I asked that before?
None of your examples in post #11 had more than 3 consecutive numbers.

Try this instead.

Samgraphics.xlsm
	C	D	E	F	G
1	1	2	3	4	5
2	1	2	3	4	6
3	1	2	3	4	7
4	1	2	3	4	8
5	1	2	3	4	9
6	1	2	3	4	10
7	1	2	3	4	11
8	1	2	3	4	12
9	1	2	3	4	13
10	1	2	3	4	14
11	1	2	3	4	15
12	1	2	3	10	16
13
14	1	26	30	34	35
15	1	26	31	32	33
16	1	26	31	32	34
17	1	26	31	32	35
18	1	26	31	33	34
19	1	26	31	33	35
20	1	26	31	34	35
21	1	26	32	33	34
22	1	26	32	33	35
23	1	26	32	34	35
24	1	26	33	34	35
25	1	27	28	29	30
26	1	27	28	29	31
27	1	27	28	29	32
28	1	27	28	29	33
29	1	27	28	29	34
30	1	27	28	29	35
31	1	27	28	30	31
32	1	27	28	30	32
3 consecutive (2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C1:G32	Expression	=AND(FIND("|1|1|","|"&$D1-$C1&"|"&$E1-$D1&"|"&$F1-$E1&"|"&$G1-$F1&"|"),ISERROR(FIND("|1|1|1|","|"&$D1-$C1&"|"&$E1-$D1&"|"&$F1-$E1&"|"&$G1-$F1&"|")))	text	NO

 

[Samgraphics]

Not exactly. What I helped with was if all 5 numbers were consecutive (or all 5 had an equal gap). My previous suggestions would not highlight if 4 of the numbers were consecutive but not 5.



Perhaps now then you see why I asked that before?
None of your examples in post #11 had more than 3 consecutive numbers.

Try this instead.

Samgraphics.xlsm
	C	D	E	F	G
1	1	2	3	4	5
2	1	2	3	4	6
3	1	2	3	4	7
4	1	2	3	4	8
5	1	2	3	4	9
6	1	2	3	4	10
7	1	2	3	4	11
8	1	2	3	4	12
9	1	2	3	4	13
10	1	2	3	4	14
11	1	2	3	4	15
12	1	2	3	10	16
13
14	1	26	30	34	35
15	1	26	31	32	33
16	1	26	31	32	34
17	1	26	31	32	35
18	1	26	31	33	34
19	1	26	31	33	35
20	1	26	31	34	35
21	1	26	32	33	34
22	1	26	32	33	35
23	1	26	32	34	35
24	1	26	33	34	35
25	1	27	28	29	30
26	1	27	28	29	31
27	1	27	28	29	32
28	1	27	28	29	33
29	1	27	28	29	34
30	1	27	28	29	35
31	1	27	28	30	31
32	1	27	28	30	32
3 consecutive (2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C1:G32	Expression	=AND(FIND("|1|1|","|"&$D1-$C1&"|"&$E1-$D1&"|"&$F1-$E1&"|"&$G1-$F1&"|"),ISERROR(FIND("|1|1|1|","|"&$D1-$C1&"|"&$E1-$D1&"|"&$F1-$E1&"|"&$G1-$F1&"|")))	text	NO

Thank you so much and yes I totally get it now, sorry again. Thank you once again.

 

[Peter_SSs]

You're welcome.