Searching for a cells text in a string


Board Regular
Dec 20, 2004

I am comparing 2 speadsheets / workbooks to see if the data on one sheets is on the other sheet and highlighting it if it is. The following code is fine for looking to see if the 2 cells match exactly but I now need another loop to go through column F which contains the same text but in the middle of a string in the cells. I need this to be highlighted also. Can anyone help? Thank you


For MY_ROWS_1 = 2 To Sheets("Sheet1").Range("A65536").End(xlUp).Row
For MY_ROWS_2 = 2 To Sheets("A").Range("A65536").End(xlUp).Row
If Sheets("Sheet1").Range("F" & MY_ROWS_1).Value = _
Sheets("A").Range("K" & MY_ROWS_2).Value Then
Sheets("A").Range("K" & MY_ROWS_2).EntireRow.Font.ColorIndex = 5
End If

Next MY_ROWS_2
Next MY_ROWS_1

End Sub

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.


Well-known Member
Feb 23, 2006
try these codes
Sub leonard()
Dim a As Long, b As Long, x As Long
x = Sheets("sheet1").Cells(Rows.Count, 6).End(xlUp).Row
y = Sheets("A").Cells(Rows.Count, 11).End(xlUp).Row
    For a = 2 To x
        For b = 2 To y
            If InStr(Sheets("A").Cells(b, 11), Sheets("sheet1").Cells(a, 6)) > 0 Then
            Sheets("A").Cells(b, 11).Interior.ColorIndex = 5
            End If
        Next b
    Next a
MsgBox "complete"
End Sub


MrExcel MVP, Moderator
May 28, 2005
Office Version
  1. 365
  1. Windows

I'd like to know a bit more about your data and requirements. For example, for you existing code:
- Are there any duplicates in 'Sheet1' column F?
- Are there any duplicates in 'A' column K?

For your proposed code:
- Do you mean you want to see if the text in 'Sheet1' column F if contained in any values in 'A' column F? I wasn't sure about what columns on what sheet are being compared for the partial match and also which sheet has the shorter values and which one the longer values.

However, I can make some comments about your existing code.

1. It could definitely be sped up, as you should not need to loop through every cell in both columns. Just how much and how, depends partly on your answers to the questions above.

2. I can see no use for

However, my 'guess' is that you are only expecting at most 1 match for each value checked. Which again comes back to my question about duplicates.

If you don't have a very big data set you won't notice the difference, but you could try this faster code for your original problem (exact match). It assumes there are not duplicates in col K of sheet 'A', or if there are, you only want the first one coloured.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> CheckCols1()<br>    <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range, rLookFor <SPAN style="color:#00007F">As</SPAN> Range, rLookIn <SPAN style="color:#00007F">As</SPAN> Range, Found <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    <SPAN style="color:#00007F">With</SPAN> Sheets("Sheet1")<br>        <SPAN style="color:#00007F">Set</SPAN> rLookFor = .Range("F2", .Range("F" & Rows.Count).End(xlUp))<br>    End <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Sheets("A")<br>        <SPAN style="color:#00007F">Set</SPAN> rLookIn = .Range("K2", .Range("K" & Rows.Count).<SPAN style="color:#00007F">End</SPAN>(xlUp))<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> rLookFor<br><SPAN style="color:#00007F">Set</SPAN> Found = rLookIn.Find(What:=c.Value, LookIn:=xlValues, Lookat:=xlWhole, _<br>    MatchCase:=False, SearchFormat:=False)<br>        <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Found <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>            Found.EntireRow.Font.ColorIndex = 5<br>        End <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> c<br>End <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

If there can be duplicates in 'A' col K and you want all matches coloured, this code would need some changes.

Your second problem could use a similar code. You may need to target different columns in each sheet (refer my questions again) but the basic change would be that in this line
Rich (BB code):
Set Found = rLookIn.Find(What:=c.Value, LookIn:=xlValues, Lookat:=xlWhole, _
    MatchCase:=False, SearchFormat:=False)
you would just change the red part to xlPart

So you may like to have a play with that yourself before posting back.
Last edited:

Watch MrExcel Video

Forum statistics

Latest member
Dual Showman

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
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 "".
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