Conditional Formatting Consecutive numbers

serge

Well-known Member
Joined
Oct 8, 2008
Messages
1,266
Office Version
  1. 2007
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,224
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

Well-known Member
Joined
Oct 8, 2008
Messages
1,266
Office Version
  1. 2007
Platform
  1. Windows
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

Well-known Member
Joined
Oct 8, 2008
Messages
1,266
Office Version
  1. 2007
Platform
  1. Windows
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

Well-known Member
Joined
Oct 8, 2008
Messages
1,266
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,405
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

Well-known Member
Joined
Oct 8, 2008
Messages
1,266
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,405
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

Well-known Member
Joined
Oct 8, 2008
Messages
1,266
Office Version
  1. 2007
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,216
Messages
5,594,886
Members
413,947
Latest member
gizmolucy

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top