Excel VBA IF THEN Statement (and a useful tip)

... also learn how to loop through cells

Excel VBA IF THEN StatementExcel VBA IF THEN Statement is one of the most useful statements in VBA.

In this tutorial, you’ll quickly learn how to work with the IF, THEN, ELSE, ELSEIF as well as the AND statements.

This way you can write Excel macros that are dependent on multiple conditions.

We also take a look at looping through a range of cells using Excel table referencing. I use the FOR EACH VBA construct for looping. During the loop, we check the value in the cell and change the background color of the cell depending on the value.

In addition to learning the IF THEN statements and how to loop through cells, you also learn how to quickly get the color code of a specific cell so you can refer to it in your Excel macro.

I’ll show you a trick with the immediate window and how you can test, and query code in there.

Watch the steps in this video:

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.

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.

Free Excel Download

Unlock Excel VBA & Macros Course is here.

Save time. Achieve more.

Over 50 Excel macro examples for download & useful VBA codes you can use for your work.

Redeem your coupon for 75% off below.

Get 75% off the course
Free Ebook

2 Comments

  1. YasserKhalil August 30, 2018 at 7:14 pm - Reply

    Awesome and great tutorial

    • Leila Gharani September 3, 2018 at 4:03 pm - Reply

      Thank you!

Leave A Comment

Share This