Searching for a cells text in a string

matt_leonard

Board Regular
Joined
Dec 20, 2004
Messages
51
Hi,

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

MY_MATCH = 0

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
MY_MATCH = 1
End If

Next MY_ROWS_2
MY_MATCH = 0
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.

ravishankar

Well-known Member
Joined
Feb 23, 2006
Messages
3,566
Hi
try these codes
Code:
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
Ravi
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
Matt

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
MY_MATCH = 0
and
MY_MATCH = 1

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

Threads
1,122,469
Messages
5,596,315
Members
414,053
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 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
Top