Highlighting code works but doesn't scan existing number

[Klombo120]

Hey Guys,

I have a code that's job is to scan a list of numbers thats on a separate tab. Then, it is supposed to scan the rest of the workbook(10 tabs) and highlight any numbers from the list in the column C, Green. It does work, however the problem is that it only highlights the numbers in the workbook that are just entered and not pre-existing numbers. When I open the document, I need the code to scan every number and highlight the numbers in the workbook regardless of it was just entered or if it is new to the list. The code is posted below and any help would be greatly appreciated!

THANKS!

'if the number you input isn't in Column C, you won't get anything
If Not Intersect(Target, Range("C2:C500")) Is Nothing Then

'looks through OvM Jobs tab
Dim lastrow As Long
lastrow = Sheet14.Range("D" & Rows.Count).End(xlUp).Row

'Must use uppercase letters in SONs'

For Each cell In Sheet14.Range("D2:D" & lastrow & "")

If Left(Target, 10) = Left(cell, 10) Then


'if the job matches, turn the cell Green
Target.Interior.Color = vbGreen
'exit the sub so it doesn't continue looking
Exit Sub

End If

Next cell
End If

[ranman256]

Remove the EXIT SUB after 'exit the sub so it doesn't continue looking

[Klombo120]

Remove the EXIT SUB after 'exit the sub so it doesn't continue looking

It didn't change anything. Thanks for the try though. Any other ideas?

[ranman256]

sorry...not sure about the loop. I test.

[ranman256]

I dont see where : TARGET gets assigned ( in Target.Interior.Color =..)
It has no value.

[ranman256]

The line: For Each cell In Sheet14.Range("D2:D" & lastrow )
doesnt need the last quotes...but i dont hurt neither.

[Klombo120]

I dont see where : TARGET gets assigned ( in Target.Interior.Color =..)
It has no value.

I'm sorry, I should've included the entire code and not just a portion


Private Sub Worksheet_Change(ByVal Target As Range)

'if the worksheet change is in the correct range, format it
If Not Intersect(Target, Range("A2:S47")) Is Nothing Then

Target.Font.Name = "Arial"
Target.Font.Size = 18
Target.Font.FontStyle = "Bold"
Target.HorizontalAlignment = xlCenter

With Target.Borders(xlEdgeBottom)
.Weight = xlThick
.LineStyle = xlContinuous
End With
With Target.Borders(xlEdgeTop)
.Weight = xlThick
.LineStyle = xlContinuous
End With
With Target.Borders(xlEdgeRight)
.Weight = xlThick
.LineStyle = xlContinuous
End With
With Target.Borders(xlEdgeLeft)
.Weight = xlThick
.LineStyle = xlContinuous
End With
Else:
'do nothing
End If


'if the number you input isn't in Column C, you won't get anything
If Not Intersect(Target, Range("C2:C500")) Is Nothing Then

'looks through OvM Jobs tab
Dim lastrow As Long
lastrow = Sheet14.Range("D" & Rows.Count).End(xlUp).Row

'Must use uppercase letters in SONs'

For Each cell In Sheet14.Range("D2:D" & lastrow & "")

If Left(Target, 10) = Left(cell, 10) Then


'if the job matches, turn the cell Green
Target.Interior.Color = vbGreen
'exit the sub so it doesn't continue looking

End If

Next cell
End If


End Sub