Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sequence Part Two
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sequence Part Two
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sequence Part Two
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sequence Part Two
Thanks ever so much! It is exactly what I need.
Thanks a Million Steve |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Part 2 - Counting series of text/number sequence | Excel Discussion (Misc queries) | |||
Search/Match/Find ANY part of string to ANY part of Cell Value | Excel Worksheet Functions | |||
Capturing a word sequence as part of a text in a spreadsheet cell | Excel Worksheet Functions | |||
2 part macro question (sequence & order) | Excel Worksheet Functions | |||
Indicate missing number in a sequence (Part II) | Excel Discussion (Misc queries) |