Help making VBA Find Function more Efficient.

L

Liz_I3

Well-known Member
Joined
Dec 30, 2002
Messages
647
Office Version
  1. 2016
Platform
  1. Windows
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

Code:
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Sort by date Sort by votes
This avoids selects and formula cells.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Red()
<SPAN style="color:#00007F">With</SPAN> ActiveSheet.Range("c:c").SpecialCells(xlCellTypeConstants)
    <SPAN style="color:#00007F">Set</SPAN> c = .Find("DOOR HELD OPEN", LookIn:=xlValues)
    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> c <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN>
        firstAddress = c.Address
        <SPAN style="color:#00007F">Do</SPAN>
            <SPAN style="color:#007F00">'C.Interior.Pattern = xlPatternGray50</SPAN>
            c.EntireRow.Interior.ColorIndex = 3
            <SPAN style="color:#00007F">Set</SPAN> c = .FindNext(c)
        <SPAN style="color:#00007F">Loop</SPAN> <SPAN style="color:#00007F">While</SPAN> <SPAN style="color:#00007F">Not</SPAN> c <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> And c.Address <> firstAddress
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#00007F">Call</SPAN> Green
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
 
Upvote 0
Thank Just_Jon
But I what I would really like to do it use the find with an If statement.

So my macro would read down column C
Is if found any row that contain the words DOOR HELD OPEN it would highlight the row Red. If it found the word "RESTORED" it would highlight the row Yellow and so on. I want to be able to do a find on various criteria.
Is this possible? I don't have to use the find function. That was just one way I could get it to work, but I am open to all suggestions.

Thanks
L
 
Upvote 0
<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> ColorMyWorld()
<SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range, Rng <SPAN style="color:#00007F">As</SPAN> Range
Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
<SPAN style="color:#00007F">Set</SPAN> Rng = [C:C].SpecialCells(xlCellTypeConstants, xlTextValues)
<SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Rng
    <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> UCase(c.Value)
        <SPAN style="color:#00007F">Case</SPAN> "DOOR HELD OPEN"
            c.Interior.ColorIndex = 3
        <SPAN style="color:#00007F">Case</SPAN> "RESTORED"
            c.Interior.Color<SPAN style="color:#00007F">In</SPAN>dex = 4
        <SPAN style="color:#00007F">Case</SPAN> "NextConditionHere"
            <SPAN style="color:#007F00">' next colorindex assigned here</SPAN>
        <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Else</SPAN>
            c.Interior.ColorIndex = 0
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN>
<SPAN style="color:#00007F">Next</SPAN> c
Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">Set</SPAN> Rng = <SPAN style="color:#00007F">Nothing</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
<SPAN style="color:#007F00">' Use the below if there might be now-empty cells which once</SPAN>
<SPAN style="color:#007F00">' held a target string and now need to have the color set to none.</SPAN>
<SPAN style="color:#00007F">Sub</SPAN> ColorMyWorld1()
<SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range, Rng <SPAN style="color:#00007F">As</SPAN> Range
Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
<SPAN style="color:#00007F">Set</SPAN> Rng = Range("C1:C" & Cells(Rows.Count, "c").End(xlUp).Row)
<SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c In Rng
    <SPAN style="color:#00007F">Select</SPAN> Case U<SPAN style="color:#00007F">Case</SPAN>(c.Value)
        <SPAN style="color:#00007F">Case</SPAN> "DOOR HELD OPEN"
            c.Interior.ColorIndex = 3
        <SPAN style="color:#00007F">Case</SPAN> "RESTORED"
            c.Interior.ColorIndex = 4
        Case "NextConditionHere"
            <SPAN style="color:#007F00">' next colorindex assigned here</SPAN>
        Case <SPAN style="color:#00007F">Else</SPAN>
            c.Interior.ColorIndex = 0
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN>
<SPAN style="color:#00007F">Next</SPAN> c
Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">Set</SPAN> Rng = <SPAN style="color:#00007F">Nothing</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Two versions above.

EDIT If the target is NOT supposed to be case blind, change Ucase(c.Value) to c.Value above.
 
Upvote 0
Thank you
Thats exactly what I was looking for. I new that repeating the same code over and over was not practical.

This is working perfectly

Thanks again
L
 
Upvote 0
Hi Just_Jon

I got a bit exceited before I tried the code on my spreadsheet. It does work (i was using the second verison), except that I can figure out how to incorpoarte the find function in the code. The word or words that I am tring to find and then if found the row is colored. are embedded with in a string. i.e. it may say Last night at 10:00 the DOOR WAS OPEN. next row it could say I found the DOOR WAS OPEN. So if it find the DOOR WAS OPEN any were in the description on any row in column c highlight it in RED , If It find the word RESTORE any where in the descripton on any row in column C highlight it in YELLOW and so on.

L
 
Upvote 0
<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> ColorMyWorld2()
<SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range, Rng <SPAN style="color:#00007F">As</SPAN> Range, i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
<SPAN style="color:#00007F">Set</SPAN> Rng = Range("C1:C" & Cells(Rows.Count, "c").End(xlUp).Row)
<SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Rng
    <SPAN style="color:#00007F">If</SPAN> InStr(1, UCase(c.Value), "DOOR HELD OPEN") <> 0 <SPAN style="color:#00007F">Then</SPAN>
        c.Interior.ColorIndex = 3
    <SPAN style="color:#00007F">ElseIf</SPAN> InStr(1, UCase(c.Value), "RESTORED") <> 0 <SPAN style="color:#00007F">Then</SPAN>
        c.Interior.ColorIndex = 4
    <SPAN style="color:#00007F">ElseIf</SPAN> InStr(1, UCase(c.Value), "NextConditionHere") <> 0 <SPAN style="color:#00007F">Then</SPAN>
        <SPAN style="color:#007F00">' next colorindex assigned here</SPAN>
    <SPAN style="color:#00007F">Else</SPAN>
        c.<SPAN style="color:#00007F">In</SPAN>terior.ColorIndex = 0
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">Next</SPAN> c
Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">Set</SPAN> Rng = <SPAN style="color:#00007F">Nothing</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Sub</SPAN> ColorMyWorld3()
<SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range, Rng <SPAN style="color:#00007F">As</SPAN> Range, i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
<SPAN style="color:#00007F">Set</SPAN> Rng = Range("C1:C" & Cells(Rows.Count, "c").End(xlUp).Row)
<SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c In Rng
    <SPAN style="color:#00007F">If</SPAN> c.Value <SPAN style="color:#00007F">Like</SPAN> "*DOOR HELD OPEN*" <SPAN style="color:#00007F">Then</SPAN>
        c.Interior.ColorIndex = 3
    <SPAN style="color:#00007F">ElseIf</SPAN> c.Value <SPAN style="color:#00007F">Like</SPAN> "*RESTORED*" <SPAN style="color:#00007F">Then</SPAN>
        c.Interior.ColorIndex = 4
    <SPAN style="color:#00007F">ElseIf</SPAN> c.Value <SPAN style="color:#00007F">Like</SPAN> "*NextConditionHere*" <SPAN style="color:#00007F">Then</SPAN>
        <SPAN style="color:#007F00">' next colorindex assigned here</SPAN>
    <SPAN style="color:#00007F">Else</SPAN>
        c.Interior.ColorIndex = 0
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">Next</SPAN> c
Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">Set</SPAN> Rng = <SPAN style="color:#00007F">Nothing</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

A couple more samples.
 
Upvote 0
Thank you
Yes I will stay on the same posting
Sorry, I really thought maybe it was too far bacck and you or anyone else would not see it, or look at it anymore.
I will not make that mistake in the future

I will try the new version and post back my result.

Thanks so much
Liz
 
Upvote 0
<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> ColorMyWorld4()
Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
[C:C].Interior.ColorIndex = xlNone
<SPAN style="color:#00007F">With</SPAN> Range("C1:C" & Cells(Rows.Count, "C").End(xlUp).Row)
    .AutoFilter Field:=1, Criteria1:="=*DOOR HELD OPEN*"
    .SpecialCells(xlCellTypeVisible).Interior.ColorIndex = 3
    .AutoFilter Field:=1
    .AutoFilter Field:=1, Criteria1:="=*RESTORED*"
    .SpecialCells(xlCellTypeVisible).Interior.ColorIndex = 4
    .AutoFilter Field:=1
    .AutoFilter Field:=1, Criteria1:="=*NextConditionHere*"
    <SPAN style="color:#007F00">' next colorindex assigned here</SPAN>
    .AutoFilter
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
Application.Goto [C1]
Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

This might even be better ...
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
VBA FindNext not finding next
Replies
3
Views
323
Alex Blakenburg
A
C
  • Question
This is an Excel to Access porting issue - Is it possible to convert an Excel VBA FIND method to work correctly in and with Access's VBA ?
4 5 6
Replies
50
Views
1K
Trebor76
T
C
  • Question
Using the Find method to copy 4 cell values above Find = X to another sheet for every value of X found in a column
Replies
6
Views
345
chazrab
C
Y
  • Solved
Error setting value of variable1 to value of variable2
Replies
2
Views
129
Yamezz
Y
Z
  • Solved
VBA Find Start and End of a list within a range
Replies
5
Views
640
Ziggy12
Z

Forum statistics

Threads
1,214,591
Messages
6,120,429
Members
448,961
Latest member
nzskater

Share this page

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