Sequence Part Two

[smandula]

I would like to obtain numbers that are in sequence in a six column ranges.
Selecting across Columns only not rows.
Such as, A1.. F3
A B C D E F
1 1 12 33 9 10 44
2 0 3 4 13 94 15
3 2 9 10 9 10 29

9 & 10 are in sequence across columns in row 1
So are 3 & 4 in sequence in row 2
9 & 10 are in sequence in row 3

Once the sequence has been identified, 9 & 10; 3 & 4; 9 & 10
only 9 & 10 they would be highlighted by a color such as grey,
based on the required number 9

Kindly a solution was provided for all sequences, which
worked well. A refined search based on a required number
only, such as number 9. So only sequences in this case
numbers 9 & 10 would be highlighted. It could be another
number such as 1 for sequence 1 & 2 the next time around.

If the above is not possible, then to highlight number 9 with a darker
gray color, making the 9 in 9 & 10 more outstanding.

Sub Look()
Dim x As Range
With Sheets("Sheet 1")
Set x = .Range(.Range("A1"), .Range("F3"))
End With

For Each C In x
If C.Value = (C.Offset(0, 1).Value - 1) Then
Range(C, C.Offset(0, 1)).Select
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With
End If
Next

End Sub

If someone could kindly refine the above solution.

With thanks in advance

 

[Jim Thomlinson]

Why not just use conditional formatting? Here is a formula that will do what
you want. The reference cell is A5 where you place the beginning number of
the sequence. This is the format that is pasted into Cell B1

=OR(AND(B1+1 = C1, B1 = $A$5), AND(B1-1 = A1, A1 = $A$5))

 

[David]

Hi,
This is just like Jim's but with a small addition.

Sub Look()
Dim x As Range
Dim Message, Title, Default, MyValue
Message = "Please enter the first number of the sequence to look for"
Title = "Sequence Search"
Default = "0"
MyValue = InputBox(Message, Title, Default)
MyValue = CInt(MyValue)
With Sheets("Sheet1")
Set x = .Range(.Range("A1"), .Range("F3"))
End With
For Each C In x
If C.Value = MyValue Then
If C.Value = (C.Offset(0, 1).Value - 1) Then
Range(C, C.Offset(0, 1)).Select
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With
End If
End If
Next
End Sub

Hope it helps. It will ask for a number, which will be the first number in
the sequence you want to ID and highlight.

Thanks,

 

[smandula]

Thanks ever so much! It is exactly what I need.

Thanks a Million
Steve