• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need formula or Macro to highlight consecutive numbers

How can I highlight consecutive numbers like 4-5 within a spreadsheet of 1000 rows? I have tried using a formula in conditional formatting and could not get it to work Lets say it stars in B:5

Thanks
 
Hi Jack,

Can you upload an example sheet, so we can see a) the layout, and b) which cells you expect to get highlighted?
 
Thanks for replying Like, I have other features on the sheet I would like to do like "liked pairs" 77
 

Attachments

  • conscutive numbers.xlsx
    10.2 KB · Views: 2
=SUMPRODUCT(--(B13=$B13:$D13))>=2

this would highlight the same numbers as a formula in conditional formatting
 
it's unclear why you have the range B6:D6 as 2 consecutive since from other example seems it should be 3 consecutive.

But have a look , below I did formulas returning trues and falses, 2 consecutive its not yet done because I would need specification about this range to know exact rules.
 

Attachments

  • conscutive numbers.xlsx
    12.7 KB · Views: 3
it's unclear why you have the range B6:D6 as 2 consecutive since from other example seems it should be 3 consecutive.

But have a look , below I did formulas returning trues and falses, 2 consecutive its not yet done because I would need specification about this range to know exact rules.


You are correct that it it is 3 in a row, my mistake in an explanation. My goal is to high light "liked" pairs like 7-7, 2 consecutive numbers (7-8), 3 consecutive numbers (7-8-9) and then 4 (6-7-8-9) I wanted these to all be a different color.
The range I am looking for would be 0-9 f that answers your question
 
Hi Larry ,

Still some doubts :

How would the following sets of 4 digits be coloured ?

1 ...... 7 ...... 2 ...... 8

1 ...... 7 ...... 1 ...... 7

1 ...... 1 ...... 3 ...... 2

3 ...... 1 ...... 2 ...... 3

We can think of many more such examples , but can you clarify what the results should be for these 4 examples ?

Narayan
 
Hi Larry ,

Still some doubts :

How would the following sets of 4 digits be coloured ?

1 ...... 7 ...... 2 ...... 8

1 ...... 7 ...... 1 ...... 7

1 ...... 1 ...... 3 ...... 2

3 ...... 1 ...... 2 ...... 3


We can think of many more such examples , but can you clarify what the results should be for these 4 examples ?

Narayan
Hi there Narayan, I did no think of all the different combos for this. I will color ode them myself of course, I am basically wanting to find the different combos and color code them and if I miss some it no big deal. I will have to go back in and manually clear the formatting on strings like 123 and make them all one color. If I can pull out doubles example: 11 , Triples 111 and consecutives example: 12 123 1234 I will be tickled pink. Does this make sense? I know I will have to do some manual adjustments

Good to hear form you and hope all is well!!!!
 
Back
Top