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

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Related Articles

• Excel VBA: Combining If with And for Multiple Conditions
• Excel VBA Nested If Then Else in a For Next Loop
• Excel VBA to Check If String Contains Letters
• Else Without If Error VBA in Excel
• Excel VBA: Check If a Sheet Exists