Advance automate search ( Ctrl + F )

[jerk77]

Hi,

Let say I have a list of keywords to search in Excel like below (more than 20)

Milk,Apple,Meat,Money, Phone,Parking , Car,TV,Sign,Laptop (...)


If I find a cell with one of this keyword I need to highlight the column where its belong too. Sometimes one column can have multiple keywords.


I wanted to know if there is an easy way to do this task without using Ctrl + F for each keyword.
I m trying to use a conditional formatting "to highlight cell when text contain" and I have added this formula:

=OR($A$2:$DP$1516="Milk",
$A$2:$DP$1516="Apple",
$A$2:$DP$1516="Meat",
$A$2:$DP$1516="Money",
$A$2:$DP$1516="Phone",
$A$2:$DP$1516="Parking ",
$A$2:$DP$1516="Car",
$A$2:$DP$1516="TV",
$A$2:$DP$1516="Sign",
$A$2:$DP$1516="Laptop")

But it's too long (Excel can only 250 characters) and my file crashed and also I still have to highlight manually the columns.


I have try to use this macro but It's not quiet what I'm looking for :

https://www.mrexcel.com/forum/excel-questions/145696-find-records-put-into-summary-sheet-solved.html

 

[paldob]

Could you not use a For Loop? Create a named range on your first list and name it SearchItems

Sub DualLoop()

'Setting the Range to loop through each Search Item
Dim srchRng as Range, srchCel As Range
  Set srchRng = ThisWorkbook.Sheets(xxxx).Range("SearchItems")

'Setting the range to look for the item and colour the cell
Dim dataRng As Range, dataCel As Range
  Set dataRng = ThisWorkbook.Sheets(xxxxx).Range("A2:DP156")

'First Loop

For Each srchCel in srchRng
  
  'Second Loop
  For Each dataCel in dataRng
   
     If srchCel = dataCel Then dataCel.Interior.Color = vbYellow '(pick any colour you want)

  Next

Next

End Sub

P.s I'm on my mobile phone so I'll have to check if this is correct later!

 

[mumps]

When you say:

If I find a cell with one of this keyword I need to highlight the column

do you mean you want to highlight the entire column or just the cell where the keyword is found? Can there be duplicates of any keyword? For example, "Apple" appears 3 times. I assume that the keywords can be in any column. Is this correct?

 

[jerk77]

do you mean you want to highlight the entire column or just the cell where the keyword is found?

- Ideally just the headers but the entire column is fine too.

Can there be duplicates of any keyword?

- Yes ,i.e we can have 2 cells with apple, 5 cells with T.V in column A, but it doesn't matter as long as I find at list one

I assume that the keywords can be in any column. Is this correct

That's right !

 

[mumps]

Try this macro. Change the array values (in red) to include all your keywords.

Sub HighlightHeader()
    Application.ScreenUpdating = False
    Dim searchArr As Variant, foundVal As Range, i As Long
    searchArr = Array([COLOR="#FF0000"]"Milk", "Apple", "Meat", "Money", "Phone", "Parking", "Car", "TV", "Sign,Laptop"[/COLOR])
    For i = LBound(searchArr) To UBound(searchArr)
        Set foundVal = ActiveSheet.UsedRange.Offset(1, 0).Find(searchArr(i), LookIn:=xlValues, lookat:=xlWhole)
        If Not foundVal Is Nothing Then
            Cells(1, foundVal.Column).Interior.ColorIndex = 3
        End If
    Next i
    Application.ScreenUpdating = True
End Sub