In this article, we will explore three simple examples that demonstrate the practical application of nested If Then Else statements within a For Next loop. Although there are countless use cases for nested If statements, we have chosen these specific examples to provide a clear understanding of how they can be utilized within a For loop.
Example 1 – Summing Data Based on Multiple Criteria Using Nested If Within a For Loop
For the first example, we have a dataset containing details of product transactions for some customers of a shop.
We will set two criteria at the bottom (Customer and Product) and determine the sum of all the transactions that meet these criteria using a VBA code with Nested If Then Else in a For Next loop.
Here is the code:
VBA Code Syntax:
Sub Summing_Data()
Target_Customer = Range("D16").Value
Target_Product = Range("D17").Value
Total_Sum = 0
For i = 1 To 10
If Range("B5").Cells(i, 2) = Target_Customer Then
If Range("B5").Cells(i, 3) = Target_Product Then
Total_Sum = Total_Sum + Range("B5").Cells(i, 4)
End If
End If
Next i
Range("D18").Value = Total_Sum
End Sub
How Does the Code Work?
Target_Customer = Range("D16").Value
Target_Product = Range("D17").Value
First, we assign the two criteria Customer and Product to the variables in cells D16 and D17 (Target_Customer and Target_Product respectively).
Total_Sum = 0
Then we take another variable named Total_Sum and assign the value of 0 to it.
For i = 1 To 10
If Range("B5").Cells(i, 2) = Target_Customer Then
If Range("B5").Cells(i, 3) = Target_Product Then
Total_Sum = Total_Sum + Range("B5").Cells(i, 4)
End If
End If
Next i
We use a for loop to iterate through all 10 rows in our dataset. Then we use two IF statements, one for determining whether the corresponding CustomerName in the row is the same person as the Target_Customer, and the other for determining whether the corresponding Product cell is the same as Target_Product. If both criteria are satisfied, we add the value in the Price cell to the Total_Sum. This loop continues until the end of the dataset.

Range("D18").Value = Total_Sum End Sub
After completing the For loop, we record the final Total_Sum in cell D18.
To test the code, we first insert a customer and product name in cells D16 and D17 respectively.
Then we run the code. The result is 83.86.
If we change the criteria in cells D16 and D17, we will get the results accordingly.
Read More: Excel VBA: Combining If with And for Multiple Conditions
Example 2 – Conditional Formatting Using Nested If Within a For Loop
Now we will conditionally format cells using nested If Then Else in a For Next loop in VBA. In our sample dataset there are a set of numbers. We’ll highlight the cells that don’t contain positive numbers.
Here is the code utilizing nested If Then Else statements in a For Next loop.
VBA Code Syntax:
Sub Hlight_Ng_cells()
Dim rng As Range
Dim cell As Range
Set rng = Range("B5:B14")
For Each cell In rng
If IsNumeric(cell.Value) Then
If cell.Value > 0 Then
'Do Nothing
Else
cell.Interior.Color = vbYellow
End If
Else
cell.Interior.Color = vbYellow
End If
Next cell
Exit Sub
End Sub
How Does the Code Work?
Set rng = Range("B5:B14")
The range of the dataset B5:B14 is assigned to the Range type variable rng.
For Each cell In rng
A For Each loop iterates through each cell in the rng.
If IsNumeric(cell.Value) Then
If cell.Value > 0 Then
'Do Nothing
Inside the For Each loop, there are two If statements (nested Ifs). In the first If statement, we check whether the current cell contains numeric data. If the answer is True then we apply another If statement to check whether the numerical value of the cell is positive. If this is also True then we do nothing.
Else
cell.Interior.Color = vbYellow
End If
If the cell contains a nonpositive number, we change the background color of the cell to yellow.
Else
cell.Interior.Color = vbYellow
End If
On the other hand, if the cell doesn’t contain any numbers, we also change the background color of the cell to yellow.
When we run the code, it highlights those cells that don’t contain positive numbers.
Example 3 – Sorting Data Using Nested If Inside a For loop
Here, we have a dataset that contains employees’ data (ID, Name, Department and Salary).
We’ll use a VBA code to sort the data according to the Department, and then sort the same departmental data in ascending order of the salaries of the employees, using Nested IF Then Else statements in the For loop.
The VBA code is as follows:
VBA Code Syntax:
Sub SortingData()
sorted_row = 0
'Creating List of Department'
Dim Department(4) As Variant
Dim i As Integer
For i = 0 To 3
Department(i) = Choose(i + 1, "HR", "IT", "Finance", "Marketing")
Next i
'Looping Throug each Department'
For d = 0 To 3
department_IR = sorted_row + 1
'Looping Through each row in the dataset'
For iRow = 1 To 10
If Range("B5").Cells(iRow, 3) = Department(d) Then
'Incrementing the no of sorted rows'
sorted_row = sorted_row + 1
'Swaping the both rows
For c = 1 To 4
dummy = Range("B5").Cells(sorted_row, c)
Range("B5").Cells(sorted_row, c) = Range("B5").Cells(iRow, c)
Range("B5").Cells(iRow, c) = dummy
Next c
End If
Next iRow
department_FR = sorted_row
'Sorting Ascending order with respect to Salary'
Count = 0
For k = department_IR To department_FR  1
For j = department_IR To department_FR  Count  1
If Range("B5").Cells(j, 4) > Range("B5").Cells(j + 1, 4) Then
' Swapping rows
For c = 1 To 4
temp = Range("B5").Cells(j, c)
Range("B5").Cells(j, c) = Range("B5").Cells(j + 1, c)
Range("B5").Cells(j + 1, c) = temp
Next c
End If
Count = Count + 1
Next j
Next k
'Sorting Completed'
Next d
End Sub
How Does the Code Work?
Sub SortingData()
sorted_row = 0
Dim Department(4) As Variant
Dim i As Integer
For i = 0 To 3
Department(i) = Choose(i + 1, "HR", "IT", "Finance", "Marketing")
Next i
We define an array (Department) to store all the departments in it. Then we apply a For loop to assign a value to each element in the array.
For d = 0 To 3
department_IR = sorted_row + 1
For iRow = 1 To 10
Next iRow
Next d
We apply another For loop (For d…Next d) to loop through each department. Inside this For loop, we arrange each department’s data serially and with ascending salaries. Then, inside the first For loop, we use another For loop (For iRow….Next iRow) to loop through each row in the dataset.
If Range("B5").Cells(iRow, 3) = Department(d) Then
sorted_row = sorted_row + 1
'Swaping the both rows
For c = 1 To 4
dummy = Range("B5").Cells(sorted_row, c)
Range("B5").Cells(sorted_row, c) = Range("B5").Cells(iRow, c)
Range("B5").Cells(iRow, c) = dummy
Next c
End If
Inside the iRow For loop, we first check if the iteration row is from the department row using an If statement. If the iterative is actually from the same department, then we swap both rows. This gathers all the rows from the same department together.
department_FR = sorted_row
Count = 0
For k = department_IR To department_FR  1
For j = department_IR To department_FR  Count  1
If Range("B5").Cells(j, 4) > Range("B5").Cells(j + 1, 4) Then
' Swapping rows
For c = 1 To 4
temp = Range("B5").Cells(j, c)
Range("B5").Cells(j, c) = Range("B5").Cells(j + 1, c)
Range("B5").Cells(j + 1, c) = temp
Next c
End If
Count = Count + 1
Next j
Next k
Next d
End Sub
Finally, after bringing all the data from the same departments together, we use the bubble sorting algorithm to sort the same departmental data in ascending order of their salary.
After running the code, we get the following result:
All the employees from the same department are grouped together in ascending order in terms of their salary.
Read More: VBA IF Statement with Multiple Conditions in Excel
Things to Remember
 When using the Nested IF statement, you must write End IF at the end, otherwise you will get an error.
 When using multiple For loops, it is critical to enter Next in the proper position. Otherwise, the code will behave incorrectly.
Download Practice Workbook
Related Articles
 Excel VBA: If Cell Contains Value Then Return a Specified Output
 Excel VBA: If Statement Based on Cell Value
 Excel VBA to Check If String Contains Letters
 Else Without If Error VBA in Excel
 Excel VBA: Check If a Sheet Exists