CODEWITHSUNDEEP
vbaexcel
user3845477
user3845477

Reputation: 5

If/Elseif statement kept going wrong when I add it into a loop

I tried this one but the if/elseif statement kept having problem. Im not sure what's wrong with this... Thanks for any help!

Private Sub CommandButton1_Click()
    Dim i As Long
    Dim j As Long

    For i = 3 To 13 Step 3
        For j = 2 To 4
            Set curcell = Worksheets("Sheet 1").Cells(i, j)
            If curcell.Value < 0.1 Then curcell.Interior.ColorIndex = 6
            ElseIf curcell.Value >= 0.1 And curcell.Value < 0.3 Then curcell.Interior.ColorIndex = 7
            End If
        Next j
    Next i
End Sub

Upvotes: 0

Views: 55

Answers (1)

L42
L42

Reputation: 19727

As commented, this is how you write your If clause:

Private Sub CommandButton1_Click()
    Dim i As Long
    Dim j As Long, curcell As Range

    For i = 3 To 13 Step 3
        For j = 2 To 4
            Set curcell = Worksheets("Sheet 1").Cells(i, j)
            If curcell.Value < 0.1 Then
                curcell.Interior.ColorIndex = 6
            ElseIf curcell.Value >= 0.1 And curcell.Value < 0.3 Then
                curcell.Interior.ColorIndex = 7
            End If
        Next j
    Next i
End Sub

Take note of the difference:

If curcell.Value < 0.1 Then curcell.Interior.ColorIndex = 6

Above line is a self terminating one-liner If Statement.
So below line:

ElseIf curcell.Value >= 0.1 And curcell.Value < 0.3 Then curcell.Interior.ColorIndex = 7

will produce a Compile Error since it's like a floating ElseIf without If.
So if the next End If since no existing If needs to be terminated or ended since you used a one-liner If in the first condition.
I don't know if this does what you want but I just corrected the If construct.
HTH.

Upvotes: 1

Related Questions