What is wrong with this For Loop code?

[thardin]

I am trying to loop through each cell in Column U from U4 to last row to check if the percentage in the cell is greater than -.050%. In a previous macro, the format of the cells in column U is set to "0.000%." This Macro isn't working correctly.
Is it because I set the Max variable in the wrong format?
Thanks


Sub CheckII()

Dim ws As Worksheet
Dim Cell As Range
Dim lr As Long

Dim iViolations As Integer
Dim Max As Double
Max = -0.05 / 100

iViolations = 0

For Each ws In ActiveWorkbook.Worksheets
With ActiveSheet
lr = Cells(Rows.Count, "B").End(xlUp).row
For Each Cell In ws.Range("U4:U" & lr)
If Cell.Value > Max Then
'Cell.EntireRow.Interior.ColorIndex = 3
iViolations = iViolations + 1
End If
Next Cell
End With
Next ws

MsgBox iViolations


End Sub

 

[jasonb75]

Who suggested that?

I said to take the first Then out of the line that I edited, no mention of removing anything else. With that simple change the code should have worked, but cleaning up the rest of the mess and correcting the error to try and avoid several hours of replies bouncing back and forth.

Sub CheckII()
Dim ws As Worksheet, c As Range, lr As Long
Dim iViolations As Long, iMax As Double
iMax = -0.05 / 100

For Each ws In ActiveWorkbook.Worksheets
    With ws
        lr = .Cells(.Rows.Count, "B").End(xlUp).row
        For Each c In .Range("U4:U" & lr)
            If c.Value > Max And IsNumeric(c.Value) Then
                c.EntireRow.Interior.ColorIndex = 3
                iViolations = iViolations + 1
            End If
        Next Cell
    End With
Next ws

    MsgBox iViolations
End Sub

 

[thardin]

ah, i had to do:

If Cell.Value And IsNumeric(Cell.Value) > Max Then

and that seemed to work

 

[thardin]

you had the code a little out of order

 

[thardin]

It's still not working actually. That just prevented the empty rows from getting highlighted. Now when I tested it with a row having 0%, it says 0 violations when there's supposed to be 1.

 

[jasonb75]

ah, i had to do:

If Cell.Value And IsNumeric(Cell.Value) > Max Then

and that seemed to work

That is what I said to do in post 8.

See the edit to post 11 for revised code, one of your problems was that LR was not looking at the correct sheet.

Also, Max is a reserved vba keyword and as such, should not be used as a variable name. Cell is not reserved but I've changed it anyway to avoid any mix ups between Cell and Cells.

 

[thardin]

 

[jasonb75]

Change Cell to c
Also, Change Max to iMax in line 11

 

[thardin]

It's almost working. Now, in the situation if There is no data under in Row 4 and below It highlights the header rows 1-4

 

[jasonb75]

That's because your code never allowed for an empty sheet. Think this should tare care of it. You might find that it still highlights row 4 on an empty sheet. On the assumption that there will always be more than 1 row of data in the sheet, you could change If lr >=4 Then to If lr >4 Then which means there must be at least 2 rows of data for it to run on that sheet.

Sub CheckII()
Dim ws As Worksheet, c As Range, lr As Long
Dim iViolations As Long, iMax As Double
iMax = -0.05 / 100

For Each ws In ActiveWorkbook.Worksheets
    With ws
        lr = .Cells(.Rows.Count, "B").End(xlUp).row
        If lr >=4 Then
            For Each c In .Range("U4:U" & lr)
                If c.Value > iMax And IsNumeric(c.Value) Then
                    c.EntireRow.Interior.ColorIndex = 3
                    iViolations = iViolations + 1
                End If
            Next c
        End If
    End With
Next ws

    MsgBox iViolations
End Sub

 

[thardin]

I think that did it!