Help making VBA Find Function more Efficient.

[Liz_I3]

I am using the below code to change the color of an entire row if certain words or phrases are found in a description field column C. These words or phases may appear anywhere within the string but always in column C

The code is working, but I know there must a way to make it more efficient.. I am repeating the same code over and over ,just substituting the find text word or phrase each time. The first Sub will call the next and so on. I would like to have one procedure that would accomplish the same thing.


Sub Red()

With ActiveSheet.Range("c:c")
Set c = .Find("DOOR HELD OPEN", LookIn:=xlValues)

If Not c Is Nothing Then
firstAddress = c.Address

Do
'C.Interior.Pattern = xlPatternGray50
c.EntireRow.Select
Selection.Interior.ColorIndex = 3

Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
Call Green
End Sub


Sub Green()

With ActiveSheet.Range("c:c")
Set c = .Find("RESTORED", LookIn:=xlValues)

If Not c Is Nothing Then
firstAddress = c.Address

Do
'C.Interior.Pattern = xlPatternGray50
c.EntireRow.Select
Selection.Interior.ColorIndex = 4

Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
Call Puple
End Sub

Thanks
L

 

[Liz_I3]

Hi Just_Jon
I have tested both version 2 & 3 and they work great. Realy exactily what I wanted. I went with version 2 only becauese I had never used InStr before and wanted to try it.

Thank so much for your help I will also test your last version 4 since I have never use the Auto filter I will experiment with it also

Thanks
L

 

[just_jon]

I just noticed that the latter versions failed to include EntireRow in the code.

If it is still your intention to color the whole row, add it after the object ref.

 

[Liz_I3]

Hey no worries I was able to put that piece in myself. You helped me with the biggest part. Now that I see how this work, I can apply it to quite a few spreadsheet that I am asked to "clean up" not just with color but my extraxting certain words. Sometimes my code works, but it is very long, this will help me make it a bit more professional.

Thanks
L