Create macro to find and mark all cells that contain a particular "keyword"

graememy

New Member
Joined
Sep 7, 2013
Messages
4
Hi there

I have an excel spreadsheet containing many quotations said by famous people. These quotations are all in column A, taking up thousands of rows.

I want to create a macro that will find all the cells that contain a particular keyword of my choice (e.g. "happiness") and then put an "x" in column b adjacent to all the cells that contain that keyword.

Thanks very much for you assistance with this.

Cheers,
Graeme
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Hi and welcome to the board.
How about something like this?
Code:
Sub SearchDemo()
Dim LstRw As Long, SrchTerm As String, Rw As Long
LstRw = Cells(Rows.Count, "A").End(xlUp).Row

SrchTerm = InputBox("What do you want to search for?")
If Len(SrchTerm) = 0 Then Exit Sub
For Rw = 1 To LstRw
  If InStr(Cells(Rw, "A"), SrchTerm) Then Cells(Rw, "B") = "X"
Next Rw

End Sub
Note that inserting the line: Option Compare Text at the very top of your code module will make it so the inputbox entry is not case sensitive.

Hope it helps.
 
Upvote 0
Instead of an "X" in the next column, what if we color the cell yellow so it stands out more? By the way, this macro should be somewhat fast as it uses no looping. Also, I made the code perform a case-insensitive search (just in case the word appears at the beginning of a sentence as well as internal to a sentence).

Code:
Sub FindWord()
  Dim Word As String
  Word = InputBox("What text do you want to find?")
  If Len(Word) Then
    Columns("A").Interior.ColorIndex = xlColorIndexNone
    Application.ReplaceFormat.Clear
    Application.ReplaceFormat.Interior.ColorIndex = 6
    Columns("A").Replace Word, Word, xlPart, , False, , False, True
    Application.ReplaceFormat.Clear
  End If
End Sub
 
Last edited:
Upvote 0
Instead of an "X" in the next column, what if we color the cell yellow so it stands out more? By the way, this macro should be somewhat fast as it uses no looping. Also, I made the code perform a case-insensitive search (just in case the word appears at the beginning of a sentence as well as internal to a sentence).

Code:
Sub FindWord()
  Dim Word As String
  Word = InputBox("What text do you want to find?")
  If Len(Word) Then
    Columns("A").Interior.ColorIndex = xlColorIndexNone
    Application.ReplaceFormat.Clear
    Application.ReplaceFormat.Interior.ColorIndex = 6
    Columns("A").Replace Word, Word, xlPart, , False, , False, True
    Application.ReplaceFormat.Clear
  End If
End Sub

I'm trying to do a similar thing, except I have multiple words that I am searching more. And they don't vary, so how would I modify this so that it doesn't have an input text box.

I have a list of customer names and I am sorting them by whether or not they have words such as city, council, government, ect.. (I have 26 keywords).
 
Upvote 0
I'm trying to do a similar thing, except I have multiple words that I am searching more. And they don't vary, so how would I modify this so that it doesn't have an input text box.

I have a list of customer names and I am sorting them by whether or not they have words such as city, council, government, ect.. (I have 26 keywords).
Give this modified version of my function a try (replace the etc. with the rest of your list with each individual item quoted)...
Code:
Sub FindWord()
  Dim Word As Variant
  Columns("A").Interior.ColorIndex = xlColorIndexNone
  Application.ReplaceFormat.Clear
  Application.ReplaceFormat.Interior.ColorIndex = 6
  For Each Word In Array("city", "council", "government", "etc.")
    Columns("A").Replace Word, Word, xlPart, , False, , False, True
  Next
  Application.ReplaceFormat.Clear
End Sub
 
Last edited:
Upvote 0
Instead of an "X" in the next column, what if we color the cell yellow so it stands out more? By the way, this macro should be somewhat fast as it uses no looping. Also, I made the code perform a case-insensitive search (just in case the word appears at the beginning of a sentence as well as internal to a sentence).

Code:
Sub FindWord()
  Dim Word As String
  Word = InputBox("What text do you want to find?")
  If Len(Word) Then
    Columns("A").Interior.ColorIndex = xlColorIndexNone
    Application.ReplaceFormat.Clear
    Application.ReplaceFormat.Interior.ColorIndex = 6
    Columns("A").Replace Word, Word, xlPart, , False, , False, True
    Application.ReplaceFormat.Clear
  End If
End Sub
Hey!

What if I need to look for multiple words at once? (i.e. apples, bananas, pineapples)

Thanks!
 
Upvote 0
Hi and welcome to the board.
How about something like this?
Code:
Sub SearchDemo()
Dim LstRw As Long, SrchTerm As String, Rw As Long
LstRw = Cells(Rows.Count, "A").End(xlUp).Row

SrchTerm = InputBox("What do you want to search for?")
If Len(SrchTerm) = 0 Then Exit Sub
For Rw = 1 To LstRw
  If InStr(Cells(Rw, "A"), SrchTerm) Then Cells(Rw, "B") = "X"
Next Rw

End Sub
Note that inserting the line: Option Compare Text at the very top of your code module will make it so the inputbox entry is not case sensitive.

Hope it helps.
Hey!

What if I need to look for multiple words at once? (i.e. apples, bananas, pineapples)

Thanks!
Hi and welcome to the board.
How about something like this?
Code:
Sub SearchDemo()
Dim LstRw As Long, SrchTerm As String, Rw As Long
LstRw = Cells(Rows.Count, "A").End(xlUp).Row

SrchTerm = InputBox("What do you want to search for?")
If Len(SrchTerm) = 0 Then Exit Sub
For Rw = 1 To LstRw
  If InStr(Cells(Rw, "A"), SrchTerm) Then Cells(Rw, "B") = "X"
Next Rw

End Sub
Note that inserting the line: Option Compare Text at the very top of your code module will make it so the inputbox entry is not case sensitive.

Hope it helps.
Sorry I meant this one... Instead of an input box I have a cell where people can search for different keywords separated by comma. So whenever one of those keywords appear in the column the next column is marked with an "X".

Would I need a loop? Or what would be the best way to proceed? Many thanks in advance!

Please let me know if unclear.
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,108
Members
452,302
Latest member
TaMere

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