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))
--
HTH...

Jim Thomlinson


"smandula" wrote:

> 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
>
>
>
>
>
>

[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,

"Jim Thomlinson" wrote:

> 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))
> --
> HTH...
>
> Jim Thomlinson
>
>
> "smandula" wrote:
>
> > 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
> >
> >
> >
> >
> >
> >

[smandula]

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

Thanks a Million
Steve