next without for error excel VBA

Question

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

You need an End With before the Next

Gary's Student
– Gary's Student

2014-12-04 16:00:32 +00:00
Commented Dec 4, 2014 at 16:00 — Gary's Student
– Gary's Student, Commented Dec 4, 2014 at 16:00

grovesNL · Accepted Answer · 2014-12-04 16:00:35Z

7

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

answered Dec 4, 2014 at 16:00

grovesNL

6,1152 gold badges22 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

grovesNL Over a year ago

@MiloKang: Great! If you could accept my answer this will let users know that the solution worked for you. Welcome to Stack Overflow!

CustomX · Accepted Answer · 2014-12-04 16:23:38Z

2

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

edited Dec 4, 2014 at 16:23

CustomX

10.2k30 gold badges89 silver badges115 bronze badges

answered Dec 4, 2014 at 16:00

bowlturner

2,0264 gold badges26 silver badges38 bronze badges

Collectives™ on Stack Overflow

next without for error excel VBA

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related