Excel 2010 Macro Controlling the Flow If, Then and Else Statement

 

The If statement is the most common device for program flow control. Suppose you have a list of products and you want to loop through each product in the list and copy it to either a Fruits list or Vegetables list. Beginners might do this by looping through the rows twice, once to look for fruit and a second time to look for vegetables. However, you can avoid looping through twice because you can use an If...Then...Else construct on a single loop to copy each row to the correct place.

 

Conditions

Any If statement needs a condition that is being tested. The condition should always evaluate to TRUE or FALSE. Examples of simple and complex conditions:

 


If...Then...End If

After the If statement, you may include one or more program lines that execute only if the condition is met. Then close the If block with an End If line.

An example of an If statement:

Sub ColorFruitRedBold()

    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row

    For i = 2 To FinalRow

        If Cells(i, 1).Value = “Fruit” Then

Cells(i, 1).Resize(1, 3).Font.Bold = True

Cells(i, 1).Resize(1, 3).Font.ColorIndex = 3

        End If

    Next i

    MsgBox “Fruit is now bold and red”

End Sub

 

Sometimes you will want to do one set of statements if the condition is true, and another set of statements if the condition is not true. To do this with VBA, the second set of conditions would be coded after the Else statement. Still, there is only one End If statement associated with this construct.

Use the following code if you want to color the fruit red and the vegetables green:

Sub FruitRedVegGreen()

    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row

    For i = 2 To FinalRow

        If Cells(i, 1).Value = “Fruit” Then

Cells(i, 1).Resize(1, 3).Font.ColorIndex = 3

        Else

Cells(i, 1).Resize(1, 3).Font.ColorIndex = 50

        End If

    Next i

    MsgBox “Fruit is red / Veggies are green”

End Sub