Find Macro

  • Thread starter Thread starter StillLearning
  • Start date Start date
S

StillLearning

I've searched past posts and can't seem to find what I'm looking for. What I
want to do is search a worksheet for several key words and when found,
highligh the cell. Where I am searching is in worksheet "Data" and it
contains many columns and many rows. What I want to search for is in
worksheet "Search", which is a listing of words (each in its own cell) that I
want to be able to add to as time goes on (will end up being hundreds of
words). Note that sometimes the search word is part of a cell in the data
field. ie I want to search for the word "red" and be able to find it in a
cell containing "a big red box".
Thanks
 
When you find the word on the sheet Data, what do you want to do with it? The macro below will find
any instance of a word in the list staring in cell A2 of Search, and will highlight the found cells
with a red fill.

HTH,
Bernie
MS Excel MVP


Option Explicit
Sub FindValues()
Dim myC As Range
Dim myD As Range
Dim myR As Range
Dim myL As Range
Dim myFindString As String
Dim firstAddress As String

Set myL = Worksheets("Search").Range("A2")
Set myL = Range(myL, myL.End(xlDown))

For Each myR In myL
myFindString = myR.Value
With Worksheets("Data").Cells

Set myC = .Find(myFindString, LookIn:=xlValues, lookAt:=xlPart)

If Not myC Is Nothing Then
Set myD = myC
firstAddress = myC.Address
End If

Set myC = .FindNext(myC)
If Not myC Is Nothing And myC.Address <> firstAddress Then
Do
Set myD = Union(myD, myC)
Set myC = .FindNext(myC)
Loop While Not myC Is Nothing And myC.Address <> firstAddress
End If
End With
'Then do what you want with all the cells that have been found, like
myD.Interior.ColorIndex = 3
Set myC = Nothing
Set myD = Nothing
Next myR

End Sub
 
Click on the cell containing the data you want to find and run:

Sub highlighter()
v = ActiveCell.Value
Sheets("Data").Activate
For Each r In ActiveSheet.UsedRange
If InStr(r.Value, v) > 0 Then
r.Interior.ColorIndex = 6
End If
Next
End Sub
 
That is exactly what I was looking for. Thank you so much for your help.

You're welcome!

Bernie
MS Excel MVP
 
Back
Top