I'm trying to create an IF statement that does the following:

1. highlights (with red color) anything with a value greater than 1 and less than 26, and then continue with the rest of the macro and do other things (which I've successfully done).
2. if there's a value over 25, then highlight with red, produce a messagebox, and exitsub (which I've successfully done).
3. if ALL rows are = 1, then do nothing and exit sub (which i'm struggling with).

For Each C In Range("B2:B25000").Cells
  If C.Value > 1 And C.Value < 26 Then
    firstValue = C.Value
    firstAddress = C.Address
    Exit For
    
    If Not (C.Value > 1 And C.Value < 26) Then Exit Sub 'No
  
    ElseIf C.Value > 25 Then
    C.Interior.Color = VBA.ColorConstants.vbRed
    MsgBox "Too big!"
    Exit Sub
  End If
Next

C.Interior.Color = VBA.ColorConstants.vbRed 'if greater than 1 & less than 26 then Color = red

'remaining of the macro goes here

End Sub

CodePudding user response：

Use the if statements to set logic flags and then decide whether to exit sub or continue.

Option Explicit

Sub test()

    Dim ws As Worksheet, c As Range, lastrow As Long
    Dim bAllOnes As Boolean, bTooBig As Boolean
    
    Set ws = Sheet1
    bAllOnes = True
    bTooBig = False
    
    lastrow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
    For Each c In ws.Range("B2:B" & lastrow).Cells
        If Val(c.Value) > 1 Then
            bAllOnes = False
            c.Interior.Color = VBA.ColorConstants.vbRed
            If c.Value > 25 Then
                 bTooBig = True
            End If
        ElseIf Val(c.Value) < 1 Then
            bAllOnes = False
        End If
    Next
    
    If bTooBig Then
        MsgBox "Too big!", vbCritical
        Exit Sub
    ElseIf bAllOnes Then
        MsgBox "All 1's!", vbCritical
        Exit Sub
    Else
        MsgBox "Continueing"
    End If
    
End Sub

CodePudding user response：

I replaced this If Not (C.Value > 1 And C.Value < 26) Then Exit Sub

with this ElseIf Application.WorksheetFunction.max(Range("b:b")) = 1 Then Exit Sub

and it worked perfect