Excel VBA: If Statement Based on Cell Value: 2 Practical Methods

Method 1 – If Statement Based on Cell Value of a Single Cell in Excel VBA

We’ll learn to use an If statement based on the value of a single cell.

Let’s try to see if Natalia Austin passed the examination or not, that is, whether the mark in cell C3 is greater than 40 or not.

Column D contains the results of the students. If cell C3 contains a mark more significant than 40, cell D3 will contain “Passed”. Otherwise, it’ll contain “Failed”.

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

Method 2 – If Statement Based on Values of a Range of Cells in Excel VBA

Use the If statement based on the values of a range of cells in VBA. You can use a for-loop for this purpose.

Find the results of all the students with a single code. We’ll iterate through a for loop that checks all the cells in the range C3:C12 and returns 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``

Related Articles

Get FREE Advanced Excel Exercises with Solutions!

Tags:

Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

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

Advanced Excel Exercises with Solutions PDF