Excel VBA: Single Loop + If-Statement; Run-time Error 1004

Question

I want to loop through a column and compare all cells in that range with the value x of the cell "AD2". If the value of the cell i is smaller than the value x, I want cell i to be marked red. While running following code:

Sub Button10()
    Dim i As Long
    Dim x As Integer
    x = Range("AD2").Value
    If Cells(i, 26).Value < x Then
        Cells(i, 26).Font.Color = vbRed
        For i = 1 To 500
        Next i
    End If
End Sub

I get following error:

Run-time error '1004': Method '_Default' of object 'Range' failed.

Google For Loop vba Your loop is incorrect. The for loop should before and the Next after IF not inside the if. — Scott Craner
– Scott Craner, Commented Nov 3, 2017 at 13:05
After the second look, the For-Loop logic is obviously flawed, thank you for the hint! — hallkevi
– hallkevi, Commented Nov 3, 2017 at 16:09

CallumDA · Accepted Answer · 2017-11-03 13:56:51Z

4

Now that I've indented your code hopefully you can see that your logic is outside your For loop when it should be inside it.

You get an error because when you declare i As Long it is initialised with the value of 0. Therefore this line will fail because i = 0 is does not return a valid range:

If Cells(i, 26).Value < x Then

So try this:

Sub Button10()
    Dim i As Long
    Dim x As Integer
    x = Range("AD2").Value
    For i = 1 To 500
        If Cells(i, 26).Value < x Then
            Cells(i, 26).Font.Color = vbRed
        End If
    Next i
End Sub

edited Nov 3, 2017 at 13:56

answered Nov 3, 2017 at 13:07

CallumDA

12.1k6 gold badges33 silver badges52 bronze badges

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

1 Comment

hallkevi Over a year ago

Thank you for clarifying! I can see, that the For-Loop logic together with the If-Statement doesn't make sense with my old arrangement. Thank you also for the great vba-intender tool, gonna use it :).

Collectives™ on Stack Overflow

Excel VBA: Single Loop + If-Statement; Run-time Error 1004

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related