Solved Help with highlighting cells (1 Viewer)

[lookforsmt]

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]

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

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

HTH

 

[Gasman]

@Isaac
That is so much faster as well

 

[Isaac]

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.

Glad it's working for you!

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

 

[lookforsmt]

Thank you Gasman and Isaac for your time. noted the steps will study them and use it in my next task.