CODEWITHSUNDEEP
excelvbafor-loopiteration
Milo Kang
Milo Kang

Reputation: 57

next without for error excel VBA

I don't understand why this code has compile error Next without For Please help :(

Sub DefColorCodes()

    For i = 2 To 5
        Range("actReg").Value = Range("Sheet1!A" & i).Value
        ActiveSheet.Shapes.Range("actReg").Select
        With Selection.ShapeRange.Fill.ForeColor.RGB = Range(Range("actRegCode").Value).Interior.Color

    Next i

    Range("B17").Select
End Sub

Upvotes: 2

Views: 638

Answers (2)

bowlturner
bowlturner

Reputation: 2016

You are using a With statement inside your for loop with out a End With

Upvotes: 2

grovesNL
grovesNL

Reputation: 6075

You aren't ending your With. Add an End With line at some point before your Next i line.

Sub DefColorCodes()
    For i = 2 To 5
        Range("actReg").Value = Range("Sheet1!A" & i).Value
        ActiveSheet.Shapes.Range("actReg").Select
        With Selection.ShapeRange.Fill.ForeColor.RGB = Range(Range("actRegCode").Value).Interior.Color
        End With
    Next i
    Range("B17").Select
End Sub

In this case it's quite possible that you didn't mean to use a With at all:

Sub DefColorCodes()
    For i = 2 To 5
        Range("actReg").Value = Range("Sheet1!A" & i).Value
        ActiveSheet.Shapes.Range("actReg").Select
        Selection.ShapeRange.Fill.ForeColor.RGB = Range(Range("actRegCode").Value).Interior.Color
    Next i
    Range("B17").Select
End Sub

Upvotes: 7

Related Questions