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.
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 Range(“A1”).Value = “Title” Then
If Not Range(“A1”).Value = “Title” Then
If Range(“A1”).Value = “Title” And Range(“B1”).Value = “Fruit” Then
If Range(“A1”).Value = “Title” Or Range(“B1”).Value = “Fruit” Then
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:
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
MsgBox “Fruit is now bold and red”
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.