Help making VBA Find Function more Efficient.

Liz_I3

Well-known Member
Joined
Dec 30, 2002
Messages
625
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:
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
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>
 

Liz_I3

Well-known Member
Joined
Dec 30, 2002
Messages
625
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
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
<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.
 

Liz_I3

Well-known Member
Joined
Dec 30, 2002
Messages
625

ADVERTISEMENT

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
 

Liz_I3

Well-known Member
Joined
Dec 30, 2002
Messages
625

ADVERTISEMENT

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
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
<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.
 

Liz_I3

Well-known Member
Joined
Dec 30, 2002
Messages
625
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
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
<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 ...
 

Watch MrExcel Video

Forum statistics

Threads
1,118,674
Messages
5,573,597
Members
412,537
Latest member
Mohamed_5966
Top