Exit for loop to prevent overwrite

Question

I used Exit For as suggested in Excel VBA - exit for loop to fix the overwrite. I am getting Next without For error on Next j line, which is the second line to the end.

For i = 2 To lastrow

    For j = 99 To 167 Step 6

        If (Not IsEmpty(ActiveSheet.Cells(i, j)) And Not IsEmpty(Cells(i, j + 2))) Then

            Cells(i, 2).Interior.Color = vbRed
            Cells(i, 3).Interior.Color = vbRed

            Exit For


            If (Not IsEmpty(ActiveSheet.Cells(i, j)) And Not IsEmpty(Cells(i, j + 8))) Then

                Cells(i, 2).Interior.Color = RGB(146, 208, 80)
                Cells(i, 3).Interior.Color = RGB(146, 208, 80)

                Exit For

                If (Not IsEmpty(ActiveSheet.Cells(i, j)) And Not IsEmpty(Cells(i, j + 14))) Or _ 
(Not IsEmpty(ActiveSheet.Cells(i, j)) And Not IsEmpty(Cells(i, j + 20))) Then

                    Cells(i, 2).Interior.Color = vbYellow
                    Cells(i, 3).Interior.Color = vbYellow

                    Exit For


    Next j

Next i

You are missing three `End If` statements to conclude your `If` blocks — Nacorid, Nov 15 '19 at 15:36

score 1 · Accepted Answer · answered Nov 15 '19 at 16:01

Try this code with ElseIf statement

Sub test2()
    For i = 2 To lastrow
        For j = 99 To 167 Step 6
            If (Not IsEmpty(ActiveSheet.Cells(i, j)) And Not IsEmpty(Cells(i, j + 2))) Then
                Cells(i, 2).Interior.Color = vbRed
                Cells(i, 3).Interior.Color = vbRed
                GoTo NextLine
                'Exit For
            ElseIf (Not IsEmpty(ActiveSheet.Cells(i, j)) And Not IsEmpty(Cells(i, j + 8))) Then
                Cells(i, 2).Interior.Color = RGB(146, 208, 80)
                Cells(i, 3).Interior.Color = RGB(146, 208, 80)
                GoTo NextLine
                'Exit For
            ElseIf (Not IsEmpty(ActiveSheet.Cells(i, j)) And Not IsEmpty(Cells(i, j + 14))) Or (Not IsEmpty(ActiveSheet.Cells(i, j)) And Not IsEmpty(Cells(i, j + 20))) Then
                Cells(i, 2).Interior.Color = vbYellow
                Cells(i, 3).Interior.Color = vbYellow
                GoTo NextLine
                'Exit For
            End If
        Next j
NextLine:
    Next i
End Sub

Instead Exit for I use Goto NextLine to avoid overwrite. Don't know if this work because don't know how to test the code. Please let me know if is clear enough or you need more help.

Exit for loop to prevent overwrite

1 Answers1