Understanding IF loops.

Jvalades

New Member
Joined
Jul 28, 2016
Messages
40
Hello again,

I am just starting VBA and trying to practice loops.

Can you please help me understand why this code works:

Code:
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:

Code:
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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)
 
Last edited:

Jvalades

New Member
Joined
Jul 28, 2016
Messages
40
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

New Member
Joined
Jul 28, 2016
Messages
40
Ahhhh (light bulb moment)

Thank you very much!
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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
Code:
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
And if you're really just doing an Odd/Even test to color alternating rows.
Try
Code:
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

New Member
Joined
Jul 28, 2016
Messages
40
Funny, I was just playing around with the code and found that to be true.

Code:
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

New Member
Joined
Jul 28, 2016
Messages
40
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
Code:
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
 

Forum statistics

Threads
1,085,429
Messages
5,383,622
Members
401,842
Latest member
BathAntelope

Some videos you may like

This Week's Hot Topics

Top