For Loop for this code?

[thardin]

I have created this code that successfully performs the task that I want. However, I got it to work only for the first row, "E5" just to see if it worked.
This code checks if each ID is within the maximum allowable range, using the data in columns E5 and V5 in the dataset.

Sub TestII()
Dim Sheet As Worksheet
Dim cell As Range
Dim lr As Long
Dim i As Integer


If (Range("E5") = "462266446" And Range("V5") < 3.75) Then
MsgBox "Good"

ElseIf (Range("E5") = "89008P588" And Range("V5") < 3.75) Then
MsgBox "Good"

ElseIf (Range("E5") = "89008P638" And Range("V5") < 4.5) Then
MsgBox "Good"
Else
MsgBox "Bad"
End If
End Sub
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
How do I do this through the entire used range though?

I attempted to use the code below to perform the task through each row, but to highlight the violations instead of message boxes, but it doesn't quite work.
What should I change in order to perform a successful loop?

Thanks
---------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sub Test()
Dim lr As Long
Dim WS As Worksheet
Dim r As Long


lr = Cells(Rows.Count, "C").End(xlUp).Row

For r = 5 To lr

If (Columns(5).Cells(r).Value = "462266446" And Columns(22).Cells(r).Value > 3.75) Then
Rows(5).Interior.ColorIndex = 3

ElseIf (Columns(5).Cells(r).Value = "89008P588" And Columns(22).Cells(r).Value > 3.75) Then
Rows(5).Interior.ColorIndex = 3

ElseIf (Columns(5).Cells(r).Value = "89008P638" And Columns(22).Cells(r).Value > 4.5) Then
Rows(5).Interior.ColorIndex = 3
Else
MsgBox "No Violations"
End If
Next r

End Sub

 

[jasonb75]

but it doesn't quite work.

When you say that something "doesn't work" please be specific as to what it is doing incorrectly and what you expect to happen instead.

Although a little unconventional in the way that it is written, your code looks as if it should work.

 

[Ezguy4u]

While jasonb is absolutely correct in saying what doesn’t work, I am going to say let’s focus on the line “lr = Cells(Rows.Count, "C").End(xlUp).Row”. I am going to guess you should use column “D” as it should have everybody’s ID. I really don’t even see any names in column C. Actually I guess I don't really know what you want to loop on.

 

[Fluff]

Duplicate to: VBA to check each row in a column is within a preferred range?

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread.