Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find Macro
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find Macro
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 "StillLearning" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find Macro
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 -- Gary''s Student - gsnu200905 "StillLearning" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find Macro
That is exactly what I was looking for. Thank you so much for your help.
"Bernie Deitrick" wrote: 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 "StillLearning" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find Macro
That is exactly what I was looking for. Thank you so much for your help.
You're welcome! Bernie MS Excel MVP |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find max value in macro | Excel Discussion (Misc queries) | |||
Macro to find | Excel Discussion (Misc queries) | |||
Get Macro warning, but can't find Macro | Excel Worksheet Functions | |||
Find Macro | Excel Discussion (Misc queries) | |||
Macro - can't find | Excel Discussion (Misc queries) |