My VBA Excel 2013 Code isn't compiling

Question

When I run this code:

Private Sub Workbook_Open()
Dim i As Integer
Dim j As Integer
Dim range1 As Integer
Dim range2 As Integer
range1 = 53
range2 = 102

For i = range1 To range2
    For j = (range1 - 50) To (range2 - 50)

        If Cells(2, i) = Cells(2, j) Then
            If Cells(7, i) > Cells(7, j) Then
            Cells(2, i).Interior.ColorIndex = 37 'Went up; Green
            ElseIf Cells(7, i) = Cells(7, j) Then
            Cells(2, i).Interior.ColorIndex = 37 'No change; Grey
            Else
            Cells(2, i).Interior.ColorIndex = 37 'Went down; Red
        End If

    Next j
    If Cells(2, i).Interior.ColorIndex = 0 Then 'Hasn't Changed; No Fill
    Cells(2, i).Interior.ColorIndex = 37 'New Song; Blue
    End If

Next i
End Sub

An error appears saying:
Compile error: Next without For
However there definitely is a For for each Next.
So where did I go wrong?
Note: The '37's are just filler numbers, I know it appears as light blue.

Answer 1

Indenting your code in a consistent manner gives the following:

Private Sub Workbook_Open()
    Dim i As Integer
    Dim j As Integer
    Dim range1 As Integer
    Dim range2 As Integer
    range1 = 53
    range2 = 102

    For i = range1 To range2
        For j = (range1 - 50) To (range2 - 50)

            If Cells(2, i) = Cells(2, j) Then
                If Cells(7, i) > Cells(7, j) Then
                    Cells(2, i).Interior.ColorIndex = 37 'Went up; Green
                ElseIf Cells(7, i) = Cells(7, j) Then
                    Cells(2, i).Interior.ColorIndex = 37 'No change; Grey
                Else
                    Cells(2, i).Interior.ColorIndex = 37 'Went down; Red
                End If

                Next j ' <--- This Next has no For associated with it

                If Cells(2, i).Interior.ColorIndex = 0 Then 'Hasn't Changed; No Fill
                    Cells(2, i).Interior.ColorIndex = 37 'New Song; Blue
                End If
            Next i
        End Sub

You can very quickly tell by the indentation levels that the Next j has no For statement associated with it within the current If block. This is why you get your error.

I suspect that you intended to have an End If just before that Next j and therefore your code would look like:

Private Sub Workbook_Open()
    Dim i As Integer
    Dim j As Integer
    Dim range1 As Integer
    Dim range2 As Integer
    range1 = 53
    range2 = 102

    For i = range1 To range2
        For j = (range1 - 50) To (range2 - 50)

            If Cells(2, i) = Cells(2, j) Then
                If Cells(7, i) > Cells(7, j) Then
                    Cells(2, i).Interior.ColorIndex = 37 'Went up; Green
                ElseIf Cells(7, i) = Cells(7, j) Then
                    Cells(2, i).Interior.ColorIndex = 37 'No change; Grey
                Else
                    Cells(2, i).Interior.ColorIndex = 37 'Went down; Red
                End If
            End If    

        Next j

        If Cells(2, i).Interior.ColorIndex = 0 Then 'Hasn't Changed; No Fill
            Cells(2, i).Interior.ColorIndex = 37 'New Song; Blue
        End If
    Next i
End Sub