PDA

View Full Version : [SOLVED] Find each occurrence of a set of numbers in column and highlight the rows that contai



RIC63
11-25-2020, 11:29 AM
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

Paul_Hossler
11-25-2020, 12:42 PM
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

JKwan
11-25-2020, 12:57 PM
try this

p45cal
11-25-2020, 02:38 PM
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.

RIC63
11-26-2020, 12:50 AM
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