# Conditional Formatting Consecutive numbers

#### serge

##### Well-known Member
Hi everyone,

I need CF formula that will highlight 2 or 3 or 4 or 5 consecutive numbers from 5 numbers combination.

A1:E1 : 5,23,24,36,54. here 23 and 24 should be highlighted.
A1:E1 : 5,23,24,25,54. here 23,24 and 25 should be highlighted.
A1:E1 : 22,23,24,25,54. here 22,23,24 and 25 should be highlighted.
A1:E1 : 22,23,24,25,26. here 22,23,24,25 and 26 should be highlighted.

I put the example for each one, for better understanding.

Thank you in advance for any help... Serge.

### Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Given your sample range of A1:E1, the CF formula rule for cell A1 would be:
=A1=B1-1

then select B1:E1 and apply this CF formula rule:
=OR(B1=A1+1,B1=C1-1)

Hi Tom, thank you for the formula.

I have a question, my intention was to have a different color for each consecutive number, the color would make it easier to see in my chart.

For example :

Green for 2 consecutive.
Purple for 3 consecutive.
Red for 4 consecutive.
Black for 5 consecutive.

Is it possible to make it happen this way ?

I have this formula :

=ISNUMBER(MATCH(1,ABS(\$A1:\$E1-A1),0))

And I would like to know if it can be modified to highlight :

ONLY the 2 consecutive in green,
and ONLY the 3 consecutive in purple,
and ONLY the 4 consecutive in red,
and ONLY the 5 consecutive in black.

Thank you.

I'm gone take the Conditional Formatting from some of my data, that someone did for me long time ago, but it stop at 3 consecutive numbers, and I would like if someone can help me, by going further with the 4 consecutive numbers with the appropriate borders.

My data start in : C30:G30. and they are : 2,17,18,35,46

Here are the Conditional Formatting formulas ( If someone need them for their own stuff make sure you respect the order I am gone give it on this post other wise the coloring get mix up ).The color are red and green.

-------------------------------------------------------------------------
Cell : C30.
first formula : =AND(C30=D30-1,C30=E30-2)
color : Red
Borders : Top/Bottom/left.

Cell : C30.
first formula : =AND(C30=D30-1,C30<>E30-2)
color : Green
Borders : Top/Bottom/left.
-------------------------------------------------------------------------
Cell : D30.
first formula : =AND(D30=E30-1,D30=F30-2)
color : Red
Borders : Top/Bottom/left.

Cell : D30.
first formula : =AND(D30=C30+1,D30=E30-1)
color : Red
Borders : Top/Bottom.

Cell : D30.
first formula : =AND(D30<>C30+1,D30=E30-1)
color : Green
Borders : Top/Bottom/left.

Cell : D30.
first formula : =AND(D30=C30+1,D30<>E30-1)
color : Green
Borders : Top/Bottom/Right.
-------------------------------------------------------------------------
Cell : E30.
first formula : =AND(E30=F30-1,E30=G30-2)
color : Red
Borders : Top/Bottom/Left.

Cell : E30.
first formula : =AND(E30=D30+1,E30=F30-1)
color : Red
Borders : Top/Bottom.

Cell : E30.
first formula : =AND(E30=C30+2,E30=D30+1)
color : Red
Borders : Top/Bottom/Right.

Cell : E30.
first formula : =AND(E30<>D30+1,E30=F30-1)
color : Green
Borders : Top/Bottom/Left.

Cell : E30.
first formula : =AND(E30=D30+1,E30<>F30-1)
color : Green
Borders : Top/Bottom/Right.
-------------------------------------------------------------------------

Cell : F30.
first formula : =AND(F30=E30+1,F30=G30-1)
color : Red
Borders : Top/Bottom.

Cell : F30.
first formula : =AND(F30=D30+2,F30=E30+1)
color : Red
Borders : Top/Bottom/Right.

Cell : F30.
first formula : =AND(F30<>E30+1,F30=G30-1)
color : Green
Borders : Top/Bottom/Left.

Cell : F30.
first formula : =AND(F30=E30+1,F30<>G30-1)
color : Green
Borders : Top/Bottom/Right.
-------------------------------------------------------------------------

Cell : G30.
first formula : =AND(G30=E30+2,G30=F30+1)
color : Red
Borders : Top/Bottom/Right.

Cell : G30.
first formula : =AND(G30=F30+1,G30<>E30+2)
color : Green
Borders : Top/Bottom/Right.
-------------------------------------------------------------------------

Those above are working perfectly for me, here below are the formula I tried to do for the 4 consecutive numbers but for some reason ( eather there are not correct or I don't place them in the right order in each cell with the Conditional Formatting ?

Here they are :

Cell : C30.
first formula : =AND(C30=D30-1,C30=E30-2,C30=F30-3)
color : Blue
Borders : Top/Bottom/Left.

Cell : D30.
first formula : =AND(D30=E30-1,D30=F30-2,D30=G30-3)
color : Blue
Borders : Top/Bottom/Left.

Cell : D30.
first formula : =AND(D30=C30+1,D30=E30-1,D30=F30-2)
color : Blue
Borders : Top/Bottom.

Cell : E30.
first formula : =AND(E30=C30+2,E30=D30+1,E30=F30-1)
color : Blue
Borders : Top/Bottom.

Cell : F30.
first formula : =AND(F30=C30+3,F30=D30+2,F30=E30+1)
color : Blue
Borders : Top/Bottom/Right.

Cell : F30.
first formula : =AND(F30=D30+2,F30=E30+1,F30=G30-1)
color : Blue
Borders : Top/Bottom.

Cell : G30.
first formula : =AND(G30=D30+3,G30=E30+2,G30=F30+1)
color : Blue
Borders : Top/Bottom/Right.

I hope someone can help to correct the error and or tell me where to put those CF in the right order ?

Given your sample range of A1:E1, the CF formula rule for cell A1 would be:
=A1=B1-1

then select B1:E1 and apply this CF formula rule:
=OR(B1=A1+1,B1=C1-1)

Hi Tom and all,

Seeing your formulas i selected the entire range and tried
=OR(A1+1=B1,AND(A1-XFD1=1,XFD1<>""))

I was not expecting this would work, but in Excel 2010 it worked perfect!

M.

Hi Marcelo Branco,

I am running with 2007, and it work fine too, thank you.

One question, can this formula be arrange to have a different color for :

2 consecutive in green
3 consecutive in purple
4 consecutive in red
5 consecutive in black

That would really make my conditional must easier ?

Thank you, I hope it's possible ??

Serge.

Hi Serge,

If you can move your data from A1:E4 to B1:F4 (i hope), maybe this, using X as a helper column.

Formula in X1
=SUM(IF(B1:F1=C1:G1-1,1,IF(B1:F1=A1:E1+1,IF(A1:E1<>"",1))))
confirmed with Ctrl+Shift+Enter (not just Enter)
copy down till the end of your data

Select your entire data-range (B1:G4), CF New Rule
CF formula
=OR(AND(B1=A1+1,A1<>"",\$X1=2),AND(B1=C1-1,\$X1=2))
pick Green

CF New Rule
=OR(AND(B1=A1+1,A1<>"",\$X1=3),AND(B1=C1-1,\$X1=3))
pick Purple

CF New Rule
=OR(AND(B1=A1+1,A1<>"",\$X1=4),AND(B1=C1-1,\$X1=4))
pick Red

CF New Rule
=OR(AND(B1=A1+1,A1<>"",\$X1=5),AND(B1=C1-1,\$X1=5))
pick Yellow

I got this

A ...F
<TABLE style="WIDTH: 288pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=384><COLGROUP><COL style="WIDTH: 48pt" span=6 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 width=64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64 align=right>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BACKGROUND: #7030a0; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; mso-pattern: black none" class=xl63 width=64 align=right>12</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BACKGROUND: #7030a0; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; mso-pattern: black none" class=xl63 width=64 align=right>13</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BACKGROUND: #7030a0; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; mso-pattern: black none" class=xl63 width=64 align=right>14</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64 align=right>20</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: red; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; mso-pattern: black none" class=xl63 align=right>11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: red; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; mso-pattern: black none" class=xl63 align=right>12</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: red; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; mso-pattern: black none" class=xl63 align=right>13</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: red; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; mso-pattern: black none" class=xl63 align=right>14</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>50</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>21</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: #92d050; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; mso-pattern: black none" class=xl63 align=right>12</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: #92d050; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; mso-pattern: black none" class=xl63 align=right>13</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>34</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: yellow; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; mso-pattern: black none" class=xl63 align=right>11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: yellow; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; mso-pattern: black none" class=xl63 align=right>12</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: yellow; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; mso-pattern: black none" class=xl63 align=right>13</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: yellow; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; mso-pattern: black none" class=xl63 align=right>14</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: yellow; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; mso-pattern: black none" class=xl63 align=right>15</TD></TR></TBODY></TABLE>

HTH

M.

Hi Marcelo Branco,

Thank you for your formulas, but it doesn't work for me as I need it !!

I have all those formulas in my example above, is because I need to have the borders all around the consecutive numbers.

With your formulas when I have : 8,9,13,14,15. all the numbers are in yellow, but it should be actually be in green for 8,9 and purple for 13,14,15 because there 2 consecutive and 3 consecutive.

or when I have 5,6,18,45,46. It should be in green for 5,6 and 45,46 but your formula highlight them in red, which is normal according to the formula but it's not what I need.

I would like to keep the above formula and add the conditional formatting for the 4 consecutive

Thank you for your respond and your work I really appreciate it.

Best Regards... Serge.

Replies
3
Views
205
Replies
12
Views
405
Replies
12
Views
221
Replies
6
Views
202
Replies
8
Views
181

1,203,171
Messages
6,053,885
Members
444,692
Latest member
Queendom

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back