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