Thank you Isaac for the code, it works perfectly and coded only those which appear in sheet ABC.
I will go through your code to understand it.
I thank you again for helping me here. Really appreciate the time taken.
Ok, I stand corrected on the .Value property.
However I am really just guessing from the title of this thread and that code.?
Ok, had a quick play.
Firstly your logic if flawed. The reason you have some values highlighted that are not on ABC sheet,is that the string being searched is in the cell as part of the value.? Eg 801 is in 4801 ?
Try you logic in small steps minimising the data searched to get the logic working.
Here is what I amended your code to.? I would also use single cell range as I did for rng instead of numerics?, but that is up to you.
You only had 100 rows in that sheet yet you were using AX100 & i ?
Get the logic working with a subset of the data, then apply to the whole sheet.?
Code:
ub MattWinter()
Dim s1 As Worksheet, s2 As Worksheet
Dim lr As Long, lr2 As Long
Dim i As Long, j As Long
Dim rng As Range
Set s1 = Sheets("Numbering")
Set s2 = Sheets("ABC")
' lr = s1.Range("A1:AX100" & Rows.Count).End(xlUp).Row
lr = s1.Range("A" & s1.Rows.Count).End(xlUp).Row
lr2 = s2.Range("C" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
For i = 2 To lr
For j = 2 To lr2
' If InStr(s1.Range("A1:AX100" & i), s2.Range("C" & j)) > 0 Then
For Each rng In s1.Range("A1:H100")
If InStr(rng, s2.Range("C" & j)) > 0 Then
rng.Interior.ColorIndex = 6
End If
Next
Next j
Next i
Application.ScreenUpdating = True
MsgBox "Review Completed"
End Sub
Thank you Isaac for the code, it works perfectly and coded only those which appear in sheet ABC.
I will go through your code to understand it.
I thank you again for helping me here. Really appreciate the time taken.
@Gasman I kind of got lucky there, making it faster wasn't even a thought I had, but the Find method is something I've been using so much recently, it was on my mind - then after the fact I noticed I'd eliminated a loop, mostly by "mental accident".