Consulting

Results 1 to 5 of 5

Thread: Find each occurrence of a set of numbers in column and highlight the rows that contai

  1. #1
    VBAX Regular
    Joined
    Jan 2015
    Posts
    92
    Location

    Find each occurrence of a set of numbers in column and highlight the rows that contai

    I searched all the posts for something that could help me to get this functionality but I found nothing.
    I would like to know if it is possible to search in a column ( see in the attached file N4-N311 ) a specific sequence made up of 3 numbers and when it is found, highlight the three rows ( in the example the first occurrence is represented by rows 32,33 and 34 ) which contain the numbers by selecting them through the row identifier ( as shown when opening the attached file ) and it would be optimal to be able to ask to search for the next occurrence using a button (such as the 'Next' button at the top of the sheet).


    thank you for any suggestions
    Attached Files Attached Files

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    For now I just highlighted the matching numbers in Col A


    Option Explicit
    
    
    Sub SandH()
        Dim rData As Range
        Dim aryData As Variant, aryMatch() As Long, aryTemp As Variant
        Dim i As Long, j As Long, iOffset As Long
        Dim sMatch As String
        
        
        sMatch = ActiveSheet.Range("I1").Value      '   <<<<< change
        aryTemp = Split(sMatch, " ")                '   start at 0
        
        ReDim aryMatch(LBound(aryTemp) + 1 To UBound(aryTemp) + 1)
        
        For i = LBound(aryTemp) To UBound(aryTemp)
            aryMatch(i + 1) = aryTemp(i)
        Next i
        
        Set rData = ActiveSheet.Range("N4")             '   <<<<<<<<<change
        aryData = Range(rData, rData.End(xlDown)).Value '   2 Dim array
        iOffset = rData.Row - 1
    
    
        For i = LBound(aryData, 1) To UBound(aryData, 1) - 2
            For j = 0 To 2
                If aryData(i + j, 1) <> aryMatch(j + 1) Then GoTo NextData
            Next j
    
    
            ActiveSheet.Cells(i + iOffset, 1).Resize(3, 1).Interior.Color = vbGreen
    
    NextData:
        Next i
    End Sub
    Edit - this can be generalized to match more than just 3
    Attached Files Attached Files
    Last edited by Paul_Hossler; 11-25-2020 at 01:55 PM.
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    VBAX Expert
    Joined
    Aug 2004
    Posts
    810
    Location
    try this
    Attached Files Attached Files

  4. #4
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Conditional formatting (highlighting) can do this.
    Instead of the three numbers being ina single cell (I1), put them in 3 separate cells (I used J1,J2,J3) then select a range (any width) starting on row 4, going as far down as necessary, then add conditional formatting with the formula:
    =OR(AND($N4=$J$1,$N5=$J$2,$N6=$J$3),AND($N3=$J$1,$N4=$J$2,$N5=$J$3),AND($N2 =$J$1,$N3=$J$2,$N4=$J$3))
    This will highlight all sets of three rows. See attached where I've only formatted column M.
    Attached Files Attached Files
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  5. #5
    VBAX Regular
    Joined
    Jan 2015
    Posts
    92
    Location
    Hi Paul_Hossler JKwan p45cal


    thanks for your help, for me all three codes are already good as well, since in real use I have data formatted with colors both on the right and on the left of the column that I have given as an example for this reason I had asked to be able to highlight the lines as when clicking on the left row identifier and holding down makes a multiple selection so the existing formatting is not affected.


    in any case thanks to all

Posting Permissions

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