+ Reply to Thread
Results 1 to 3 of 3

Highlight consecutive numbers in array

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    07-01-2012
    Location
    ZA
    MS-Off Ver
    Excel 2013
    Posts
    300

    Highlight consecutive numbers in array

    Goodday I need some help with the following macro -This check for consecutive numbers after the >> -what do I need to change in the code to only look for consecutive numbers before the >> ? Everything after the >> must be ignored.Thank you for any clarification in the code.Thank you for any help
    Sub ConsecutiveNumbers2()
    LR = Cells(Rows.Count, "G").End(xlUp).Row
    For j = 2 To LR
      s = Cells(j, "G")
      p = InStr(s, ">>")
      s1 = Right(s, Len(s) - p - 2)
      arrs = Split(s1, ",")
      For k = 0 To 4
        For i = k To 4
            If Val(arrs(k)) > Val(arrs(i)) Then
                Temp = arrs(i)
                arrs(i) = arrs(k)
                arrs(k) = Temp
            End If
        Next
      Next
      For k = 0 To 2
        If Val(arrs(k)) = Val(arrs(k + 1) - 1) Then
          Cells(j, "G").Interior.ColorIndex = 5
        End If
      Next
    Next
    
    End Sub
    My data Cell G2

    1, 10, 14, 38, 43 >> 3, 13, 3, 1, 10
    1, 10, 14, 39, 42 >> 3, 13, 3, 7, 4
    1, 10, 15, 22, 26 >> 3, 13, 12, 1, 1
    1, 10, 15, 22, 38 >> 3, 13, 12, 1, 1
    1, 10, 15, 24, 30 >> 3, 13, 12, 0, 2
    1, 10, 15, 26, 38 >> 3, 13, 12, 1, 1
    1, 10, 15, 30, 34 >> 3, 13, 12, 2, 0
    1, 10, 16, 20, 21 >> 3, 13, 4, 7, 3
    1, 10, 16, 22, 29 >> 3, 13, 4, 1, 9
    1, 10, 16, 23, 24 >> 3, 13, 4, 10, 0
    1, 10, 16, 23, 34 >> 3, 13, 4, 10, 0
    1, 10, 16, 24, 27 >> 3, 13, 4, 0, 10
    1, 10, 16, 24, 43 >> 3, 13, 4, 0, 10
    1, 10, 16, 25, 30 >> 3, 13, 4, 8, 2
    1, 10, 16, 26, 29 >> 3, 13, 4, 1, 9
    1, 10, 16, 27, 34 >> 3, 13, 4, 10, 0
    1, 10, 16, 29, 38 >> 3, 13, 4, 9, 1
    1, 10, 16, 32, 39 >> 3, 13, 4, 3, 7
    1, 10, 16, 33, 36 >> 3, 13, 4, 2, 8
    1, 10, 16, 33, 44 >> 3, 13, 4, 2, 8
    1, 10, 16, 34, 43 >> 3, 13, 4, 0, 10
    1, 10, 16, 35, 36 >> 3, 13, 4, 2, 8
    1, 10, 16, 35, 44 >> 3, 13, 4, 2, 8
    1, 10, 16, 36, 45 >> 3, 13, 4, 8, 2
    1, 10, 16, 44, 45 >> 3, 13, 4, 8, 2
    1, 10, 18, 21, 30 >> 3, 13, 9, 3, 2
    1, 10, 18, 24, 31 >> 3, 13, 9, 0, 5
    1, 10, 18, 31, 34 >> 3, 13, 9, 5, 0
    1, 10, 18, 32, 33 >> 3, 13, 9, 3, 2
    1, 10, 18, 32, 35 >> 3, 13, 9, 3, 2
    1, 10, 18, 32, 45 >> 3, 13, 9, 3, 2
    1, 10, 20, 21, 28 >> 3, 13, 7, 3, 4
    1, 10, 20, 21, 42 >> 3, 13, 7, 3, 4
    1, 10, 20, 24, 39 >> 3, 13, 7, 0, 7
    1, 10, 20, 30, 31 >> 3, 13, 7, 2, 5
    1, 10, 20, 34, 39 >> 3, 13, 7, 0, 7
    1, 10, 21, 32, 36 >> 3, 13, 3, 3, 8
    1, 10, 21, 32, 44 >> 3, 13, 3, 3, 8
    1, 10, 22, 23, 32 >> 3, 13, 1, 10, 3
    1, 10, 22, 27, 32 >> 3, 13, 1, 10, 3
    1, 10, 22, 28, 29 >> 3, 13, 1, 4, 9
    1, 10, 22, 29, 42 >> 3, 13, 1, 9, 4
    1, 10, 22, 31, 36 >> 3, 13, 1, 5, 8
    1, 10, 22, 31, 44 >> 3, 13, 1, 5, 8
    1, 10, 22, 32, 43 >> 3, 13, 1, 3, 10

  2. #2
    Forum Contributor
    Join Date
    07-01-2012
    Location
    ZA
    MS-Off Ver
    Excel 2013
    Posts
    300

    Re: Highlight consecutive numbers in array

    Bump anyone ......thank you for any help.Rep given to the person that can solve it for me

  3. #3
    Forum Contributor
    Join Date
    07-01-2012
    Location
    ZA
    MS-Off Ver
    Excel 2013
    Posts
    300

    Re: Highlight consecutive numbers in array

    bump bump bump

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1