Advance automate search ( Ctrl + F )

jerk77

New Member
Joined
Mar 8, 2019
Messages
2
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:

Code:
=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 :
HTML:
https://www.mrexcel.com/forum/excel-questions/145696-find-records-put-into-summary-sheet-solved.html
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Could you not use a For Loop? Create a named range on your first list and name it SearchItems

Code:
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!
 
Upvote 0
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?
 
Upvote 0
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 !
 
Upvote 0
Try this macro. Change the array values (in red) to include all your keywords.
Code:
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
 
Upvote 0

Forum statistics

Threads
1,223,908
Messages
6,175,305
Members
452,633
Latest member
DougMo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top