Excel VBA: If Statement Based on Cell Value (2 Practical Examples)

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

VBA If Statement Based on Cell Value in Excel


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.

Data Set to Show If Statement Based on Cell Value in Excel VBA

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

VBA If Statement Based on Cell Value in Excel

⧭ 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).

Output to Show If Statement Based on Cell Value in Excel VBA


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

If Statement Based on Cell Value in Excel VBA

⧭ Output:

Run the code from the Run Sub / UserForm 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 Then 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.

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

1 Comment
  1. Exceldemy is a great plateform for learning skills.
    I really enjoyed mi first learning in exceldemy plateform and I want more.

Leave a reply

ExcelDemy
Logo