In this article, I’ll show you how you can use an If statement in VBA in Excel based on a cell value.
Excel VBA: If Statement Based on Cell Value (Quick View)
Sub If_Statement_Based_On_a_Single_Cell()
If Range("C3").Value >= 40 Then
Range("D3").Value = "Passed"
Else
Range("D3").Value = "Failed"
End If
End Sub
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
If Statement Based on Cell Value in Excel VBA
Here we’ve got a worksheet that contains the names and marks of some students of a school in an examination.
Our objective is to learn how to use the If statement in Excel VBA based on a cell value from this data set.
1. If Statement Based on Cell Value of a Single Cell in Excel VBA
First of all, we’ll learn to use an If statement based on the value of a single cell.
For example, let’s try to see if Natalia Austin passed on the examination or not, that is, whether the mark in cell C3 is greater than 40 or not.
Column D contains the result of the students. That is, If cell C3 contains a mark greater than 40, cell D3 will contain “Passed”. Otherwise, it’ll contain “Failed”.
We’ll use a VBA Range object to create this If statement based on the cell value.
The VBA code for this will be:
⧭ VBA Code:
Sub If_Statement_Based_On_a_Single_Cell()
If Range("C3").Value >= 40 Then
Range("D3").Value = "Passed"
Else
Range("D3").Value = "Failed"
End If
End Sub
⧭ Output:
Run the code from the Run Sub / UserForm tool in the VBA toolbar.
It’ll make cell D3 contain “Failed”, as the mark in cell C3 is less than 40 (32).
Read more: If Cell Contains Value Then Return a Specified Output
2. If Statement Based on Values of a Range of Cells in Excel VBA
You can also use the If statement based on the values of a range of cells in VBA. You can use a for-loop for this purpose.
For example, here we can find out the result of all the students with a single code. We’ll iterate through a for-loop that’ll check all the cells in the range C3:C12 and return a corresponding result, “Passed” or “Failed”.
The VBA code for this will be:
⧭ VBA Code:
Sub If_Statement_Based_On_a_Range_of_Cells()
For i = 1 To Range("C3:C12").Rows.Count
If Range("C3:C12").Cells(i, 1).Value >= 40 Then
Range("D3:D12").Cells(i, 1).Value = "Passed"
Else
Range("D3:D12").Cells(i, 1).Value = "Failed"
End If
Next i
End Sub
⧭ Output:
Run the code from the Run Sub / User Form tool in the VBA toolbar. It’ll return “Passed” for the marks that are greater than 40, and “Failed” for those which are less than 40.
Things to Remember
Here I’ve shown an If statement with a single condition. But if you wish, you can use multiple conditions within an If statement.
If you use OR type multiple conditions, join them with an OR.
And if you use AND type multiple conditions, join them with an AND.
For example, to check if the mark in cell B3 is greater than 40 and less than 50 or not, use:
If (Range("C3").Value > 40 Or Range("C3").Value < 50) Then
Conclusion
Therefore, these are the ways to use an If statement in Excel VBA based on a cell value. Do you have any questions? Feel free to ask us. And don’t forget to visit our site ExcelDemy for more posts and updates.
Exceldemy is a great plateform for learning skills.
I really enjoyed mi first learning in exceldemy plateform and I want more.