# SolvedHelp with highlighting cells (1 Viewer)

#### lookforsmt

##### Registered User.
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

##### Enthusiastic Amateur
Ok, I stand corrected on the .Value property.
However I am really just guessing from the title of this thread and that code.?

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``````

HTH

#### Gasman

##### Enthusiastic Amateur
@Isaac
That is so much faster as well

#### Isaac

##### Lifelong Learner
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".

#### lookforsmt

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

Replies
6
Views
166
Replies
1
Views
51
Replies
0
Views
102
Replies
4
Views
107
Replies
21
Views
395