Search/flag for specific text within workbook

[hrexcel246]

Hi. I have a worksheet with over 20 000 rows and would like to search and flag for comments/rows that contain a specific word such as 'fees'. I think something along the lines of an IF statement combined with a SEARCH statement, but have tried various combinations, but not getting anywhere. Help?

 

[VoG]

Try

Sub FindHighlight()
Dim tempcell As Range, Found As Range, sTxt, FoundRange As Range, Response As Integer
Set Found = Range("A1")
sTxt = InputBox(prompt:="Enter value for search")
If sTxt = "" Then Exit Sub
Set tempcell = Cells.Find(What:=sTxt, After:=Found, LookIn:=xlValues, lookat:=xlPart)
If tempcell Is Nothing Then
    MsgBox prompt:="Not found"
    Exit Sub
Else
    Set Found = tempcell
    Set FoundRange = Found
End If
Do
    Set tempcell = Cells.FindNext(After:=Found)
    If Found.Row >= tempcell.Row Then Exit Do
    Set Found = tempcell
    Set FoundRange = Application.Union(FoundRange, Found)
Loop
FoundRange.Interior.ColorIndex = 6
Response = MsgBox(prompt:="Clear highlighting", Buttons:=vbOKCancel + vbQuestion)
If Response = vbOK Then FoundRange.Interior.ColorIndex = xlNone
End Sub

 

[SteveO59L]

Are you looking in a specific column?

 

[Ody]

So are you trying to incorporate a formula that does something to cells that contain the word "fee"?

Or, are you just trying to quickly find all the cells that have the word "Fee"? If this is the case, CRTL F is your friends.

 

[hrexcel246]

I need to pull out those rows with that search criteria for further analysis and summaries. The Find function would take forever for 20 000 rows representing the same in comments.

 

[SteveO59L]

=IF(ISERROR(FIND("fee",A1)),"",FIND("fee",A1))

 

[hrexcel246]

Thank you very much that did the trick. A further question, nstead of returning the character placement of the word I am searching for, is there a way to just return the word "fee"?

 

[Ody]

The following edit to the formula would return "Fee".

=IF(ISERROR(FIND("fee",A1)),"","fee")

 

[hrexcel246]

Brilliant! Thank you ALL for your insights and expertise. I spent way too much time on this, before reaching out. I can use this formula in various other applications. Thank you again. Enjoy the weekend!

 

[hrexcel246]

Another quick question. How does one ensure that both lower case and upper case words get captured? Currently only the case in the formula itself is captured?