Conditional Formatting Consecutive numbers

[serge]

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.

 

[Tom Urtis]

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)

 

[serge]

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 ?

 

[serge]

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.

 

[serge]

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 ?

Thank you in advance... Serge.

 

[Marcelo Branco]

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.

 

[serge]

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.

 

[Marcelo Branco]

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.

 

[serge]

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.