Understanding IF loops.

[Jvalades]

Hello again,

I am just starting VBA and trying to practice loops.

Can you please help me understand why this code works:

For startingnumber = 1 To endingnumber


    If startingnumber = 1 Or startingnumber = 3 Or startingnumber = 5 Or startingnumber = 7 Or startingnumber = 9 Then
    
    Cells(startingnumber, 1).Interior.Color = RGB(0, 0, 0)


    ElseIf startingnumber = 2 Or 4 Or 6 Or 8 Or 10 Then
    
    Cells(startingnumber, 1).Interior.Color = RGB(255, 0, 0)


    End If
    
Next startingnumber


End Sub

But if I were to change my code to this it will not:

For startingnumber = 1 To endingnumber

    If startingnumber = 1 Or 3 Or 5 Or 7 Or 9 Then
    
    Cells(startingnumber, 1).Interior.Color = RGB(0, 0, 0)


    ElseIf startingnumber = 2 Or 4 Or 6 Or 8 Or 10 Then
    
    Cells(startingnumber, 1).Interior.Color = RGB(255, 0, 0)


    End If
    
Next startingnumber

Thanks
JV

 

[Jonmo1]

Because each element of the OR needs to be a complete expression with a true or false result.

The same reason this won't work in a formula
=OR(A1=1,2,3,4,5)

It has to be
=OR(A1=1,A1=2,A1=3,A1=4,A1=5)

 

[Jvalades]

Jonmo,

Thanks for clarifying. One last question:

Then why does my elseif statement not get rejected? or in this case the "elseif" acts like a "Else"

Thanks,
JV

 

[Jonmo1]

This is not actually invalid syntax (there will be no error)

ElseIf startingnumber = 2 Or 4 Or 6 Or 8 Or 10 Then

But it only has one real 'expression' (startingnumber = 2)

Might help to look at it this way
ElseIf (startingnumber = 2) Or (4) Or (6) Or (8) Or (10) Then

It's expecting each of those sets of (parens) to contain an expression with true or false.
Since 4 is not actually an expression, it is considered to be TRUE
any number other than 0 is considered TRUE
So it becomes

ElseIf (startingnumber = 2) Or TRUE Or TRUE Or TRUE Or TRUE Then

Since it's an OR, then it is irrelevant if startingnumber is 2 or not, because the other arguments are all true, so the overall OR is TRUE.
That line will ALWAYS be TRUE.

 

[Jvalades]

Ahhhh (light bulb moment)

Thank you very much!

 

[Jonmo1]

You're welcome.

You really don't actually need the Elseif line anyway.
If the first IF is FALSE (it's NOT 1 or 3 or 5 or 7 or 9), then it therefor MUST be either 2 or 4 or 6 or 8 or 10
Assuming endingnumber is 10.

So you could just use Else

For startingnumber = 1 To endingnumber
    If startingnumber = 1 Or startingnumber = 3 Or startingnumber = 5 Or startingnumber = 7 Or startingnumber = 9 Then
        Cells(startingnumber, 1).Interior.Color = RGB(0, 0, 0)
    Else
        Cells(startingnumber, 1).Interior.Color = RGB(255, 0, 0)
    End If
Next startingnumber
End Sub

 

[Jonmo1]

And if you're really just doing an Odd/Even test to color alternating rows.
Try

For startingnumber = 1 To endingnumber
    If startingnumber Mod 2 = 1 Then
        Cells(startingnumber, 1).Interior.Color = RGB(0, 0, 0)
    Else
        Cells(startingnumber, 1).Interior.Color = RGB(255, 0, 0)
    End If
Next startingnumber

 

[Jvalades]

Funny, I was just playing around with the code and found that to be true.

Else    'startingnumber = 2 Or 4 Or 6 Or 8 Or 10 Then
    
    Cells(startingnumber, 1).Interior.Color = RGB(255, 0, 0)

I'm excited to start learning VBA. Starting on homeandlearning.org atm.

 

[Jvalades]

I thought their might be a way to skip through odd numbers. Incase my Ending number was larger.

I haven't read much about using "Mod". I just added it to my notes to come back to.

And if you're really just doing an Odd/Even test to color alternating rows.
Try

For startingnumber = 1 To endingnumber
    If startingnumber Mod 2 = 1 Then
        Cells(startingnumber, 1).Interior.Color = RGB(0, 0, 0)
    Else
        Cells(startingnumber, 1).Interior.Color = RGB(255, 0, 0)
    End If
Next startingnumber