The IF…THEN statement is one of the most commonly used and most useful statements in VBA. The IF…THEN statement allows you to build logical thinking inside your macro.
The IF…THEN statement is like the IF function in Excel. You give the IF a condition to test, such as “Is the customer a “preferred” customer?” If the customer is classified as “preferred” then calculate a discount amount. Another test could be to test the value of a cell, such as “Is the cell value greater than 100?” If so, display the message “Great sale!” Otherwise, display the message “Better luck next time.”
The IF…THEN can also evaluate many conditions. Like the AND function, you can ask several questions and all the questions must evaluate to TRUE to perform the action. Similarly, you can ask several questions and if any single or multiple of questions are true, the action will be performed. This is like an OR function in Excel.
Featured Course
Unlock Excel VBA & Excel Macros
Task #1 – Simple IF
In this example we will evaluate a single cell. Once we have the logic correct, we will apply the logic to a range of cells using a looping structure.
In Excel, open the VBA Editor by pressing F-11 (or press the Visual Basic button on the Developer ribbon.)
Right-click “This Workbook” in the Project Explorer (upper-left of VBA Editor) and select Insert ⇒ Module.
In the Code window (right panel) type the following and press ENTER.
Sub Simple_If()
We want to evaluate the contents of cell B9 to determine if the value is greater than 0 (zero). If the value is >0, we will display the value of cell B9 in cell C9.
In the Code window, click between the Sub and End Sub commands and enter the following.
If Range("B9").Value > 0 Then Range("C9").Value = Range("B9").Value
If you only have a single action to perform, you can leave all the code on a single line and you do not have to close the statement with an END IF.
If you have more than one action to perform, you will want to break your code into multiple lines like the following example.
When using this line-break style, don’t forget to include the END IF statement at the end of the logic.
Test the function by executing the macro. Click in the code and press F5 or click the Run 
button on the toolbar at the top of the VBA Editor window.
The number 45 should appear in cell C9.
If we change the value in cell B9 to -2, clear the contents of cell C9 and re-run the macro, cell C9 will remain blank.
Suppose we want to test the values in Column B to see if they are between 1 and 400. We will use an AND statement to allow the IF to perform multiple tests.
Update the code with the following IF statement.
Sub Simple_If()
If Range("B9").Value > 0 And Range("B9").Value <= 400 Then
Range("C9").Value = Range("B9").Value
End If
End Sub
Test the macro by changing the value in cell B9 to values between 1 and 400 as well as testing values >400 or <=0 (remember to clear the contents of cell C9 prior to each test).
Task #2 – Color all values between 1 and 400 green (Looping Through Data)
Now we want to loop through the values in Column B and perform the test on each value.
Below the existing procedure, start a new procedure named IF_Loop(). Type the following and press ENTER.
Sub IF_Loop()
We want to color all the cells in range B9:B18 green if their cell value is between 1 and 400.
There are many ways to determine the data’s range. For examples of several range detection/selection techniques, click the link below to check out the VBA & Macros course.
Unlock Excel VBA and Excel Macros
The technique we will use is to convert the plain table to a Data Table and use Table References. Table References are great because they automatically expand and contract when data is either added or removed from the table. This keep you from having to update all your formulas and VBA code when data ranges change, which they often do.
Click anywhere in the table and press CTRL-T and then click OK.
We want to restore our original cell colors. Select Table Tools ⇒ Design ⇒ Table Styles (group) ⇒ Expand the table styles list and select Clear (bottom of list).
Rename the table (upper-left) to “TableSales”.
Select cell D7 (or any blank cell) and type an equal’s sign.
=Select cells B9:B18 and note the update to the formula. This contains the proper method for referring to table fields (columns).
=TableSales[Sales]
Highlight the reference in the formula bar (do not include the equal’s sign) and press CTRL-C to copy the reference into memory.
Press ESC to abandon the formula.
Return to the VBA Editor and click between the Sub and End Sub commands in the IF_Loop() procedure.
We want to loop through the sales column of the table. For detailed information on creating and managing looping structures, click the link below.
Excel VBA: Loop through cells inside the used range (For Each Collection Loop)
We want the cell color to change to green if the cell’s value is between 1 and 400. We can use the Interior object to set the Color property to green. Enter the following code in the VBA Editor.
Sub IF_Loop()
Dim cell As Range
For Each cell In Range("TableSales[Sales]")
If cell.Value > 0 And cell.Value <= 400 Then
cell.Interior.Color = VBA.ColorConstants.vbGreen
End If
Next cell
End Sub
Run the updated code to see the results as witnessed below.
The color green is a bit on the bright side. We want a pale green, but we don’t know the color code for pale green. Here is a great way to discover the color of any cell.
Open the Immediate Windows by pressing CTRL-G or clicking View à Immediate Window from the VBA Editor toolbar. This should present the Immediate Window in the lower portion of the VBA Editor.
Select an empty cell and set the fill color to a pale green.
With the newly colored cell selected, click in the Immediate Window of the VBA Editor and enter the following text and press ENTER.
? activecell.Interior.Color
The above command will return the value of the active cell’s fill color. In this case, pale green is 9359529.
Update the VBA code to use the color code for pale green instead of the VBA green.
cell.Interior.Color = 9359529
Run the updated code and notice the colors are a more pleasant pale green.
Task #3 – Color all cells that are <=0 and >400 yellow
Now that we have identified all the numbers between 1 and 400, let’s color the remaining cells yellow. The color code for the yellow we will be using is 6740479.
To add the second possible action will require the addition of an ELSE statement at the end of our existing IF statement. Update the code with the following modification.
Else
cell.Interior.Color = 6740479
Run the updated code and notice how all the previously white cells are now yellow.
Task #4 – Negative values will be colored red
We will now update the code to color all the negative valued cells red. The color code for the red we will be using is 192.
To add the third possible action will require the addition of an ELSEIF statement directly after the initial IF statement. Update the code with the following modification.
ElseIf cell.Value < 0 Then
cell.Interior.Color = 192
Run the updated macro and observe that all the negative valued cells are now filled with the color red.
Feel free to Download the Workbook HERE.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
