Using a VBA IF Statement with Multiple Conditions in Excel – 8 Methods

Method 1 – Using a VBA IF Statement with Multiple Conditions: IF with OR

Consider the following macro.

Sub IfWithOr()
If 5 < 10 Or 10 < 5 Then
    MsgBox "One true condition exists!"
Else
    MsgBox "No true condition exists!"
End If
End Sub

In the second line of the code, two conditions were inserted in the IF statement with the OR function. The macro line If 5 < 10 Or 10 < 5 Then means, if 5 is less than 10 Or if 10 is less than 5, a result will be returned. As one condition (5 < 10) here is true and the other condition (10 < 5) is false, it will return TRUE. Here, a custom message in the MsgBox dialog box will be returned.

• Run the code. It will return “One true condition exists!”, which is the first custom message in the MsgBox.

To get the return result as FALSE, provide false conditions in the IF statement:

Sub IfCondWithOr()
If 4 < 2 Or 10 < 5 Then
    MsgBox "One true condition exists!"
Else
    MsgBox "No true condition exists!"
End If
End Sub

In the second line of the code, two conditions were inserted in the IF statement with the OR function. The macro line If 4 < 2 Or 10 < 5 Then means, if 4 is less than 2 Or if 10 is less than 5, it returns a result. As both conditions are false, it will return FALSE. The second custom message in the MsgBox dialogue box will be returned.

• Run the code. It will return “No true condition exists!”, which is the second custom message in the MsgBox.

IF with OR in a Range in Excel

You can also perform the IF statement with the OR function to get results when you have a value in your Excel worksheet.

Enter the sheet code name, followed by the range whose value you want to measure and insert the condition.

If you have a numeric value in B5 and want to know if the value is less than or greater than 10, enter this code:

If Sheet1.Range(“B5”).Value > 10 Then

Sheet1 = code name of the worksheet

• Use the code:

Sub IfWithOrRange()
If Sheet1.Range("B5").Value > 10 Then
    MsgBox "Value in cell B5 is greater than 10"
Else
    MsgBox "Value in cell B5 is less than 10"
End If
End Sub

• Run the code. If the value in B5 is less than 10 – which is true here:  B5 holds 5 -, the custom message “Value in cell B5 is less than 10” will be displayed in the MsgBox dialog box.

If the cell has a value greater than 10 – B7 is 15 -, the other message will be returned.

Sub IfCondWithOrRange()
If Sheet1.Range("B7").Value > 10 Then
    MsgBox "Value in cell B7 is greater than 10"
Else
    MsgBox "Value in cell B7 is less than 10"
End If
End Sub

• Run this code. The custom message “Value in cell B7 is greater than 10” will be displayed in the MsgBox dialog box.

Read More: Excel VBA: If Statement Based on Cell Value

Method 2 – Using a VBA IF Statement with Multiple Conditions: IF with AND

Consider the following macro.

Sub IfWithAnd()
If 2 < 4 And 5 < 10 Then
    MsgBox "Both conditions are true!"
Else
    MsgBox "Only one condition is true!"
End If
End Sub

In the second line of the code, two conditions were inserted in the IF statement with the AND function. The macro line If 2 < 4 Or 5 < 10 Then means, if 2 is less than 4 AND if 5 is less than 10, it returns a result. As both conditions are true, it will return TRUE. The first custom message in the MsgBox dialog box will be returned.

• Run the code. It will return “Both conditions are true!”, which is the first custom message in the MsgBox.

To get the return result as FALSE, provide at least one false condition in the IF statement:

Sub IfCondWithAnd()
If 2 < 4 And 10 < 5 Then
    MsgBox "Both conditions are true!"
Else
    MsgBox "Only one condition is true!"
End If
End Sub

In the second line of the code, two conditions were inserted in the IF statement with the AND function. The macro line If 2 < 4 AND 10 < 5 Then means, if 2 is less than 4 AND if 10 is less than 5, it returns a result. As one condition (2 < 4)  is true and the other (10 < 5) is false, it will return FALSE. The second custom message in the MsgBox dialog box will be returned.

If you run the code, it will return “Only one condition is true!”, which is the second custom message in the MsgBox.

IF with Multiple AND in Excel

• Consider the following macro.

Sub IfWithMultipleAnd()
If 2 < 4 And 10 > 5 And 10 - 5 = 5 Then
    MsgBox "All conditions are true"
Else
    MsgBox "Some conditions are true"
End If
End Sub

In the second line of the code, three conditions were inserted in the IF statement with the AND function. The macro line If 2 < 4 And 10 > 5 And 10 – 5 = 5 Then means, if 2 is less than 4 AND if 5 is less than 10 AND if 5 subtract from 10 is 5, it returns a result. As all conditions are true, it will return TRUE. The first custom message in the MsgBox dialog box will be returned.

If you run the code, it will return “All conditions are true”, which is the first custom message in the MsgBox.

To get the return result as FALSE, provide at least one false condition in the IF statement:

Sub IfCondWithMultipleAnd()
If 2 < 4 And 10 > 5 And 10 - 5 = 0 Then
    MsgBox "All conditions are true"
Else
    MsgBox "Some conditions are true"
End If
End Sub

In the second line of the code, three conditions were inserted in the IF statement with the AND function. The macro line If 2 < 4 And 10 > 5 And 10 – 5 = 0 Then means, if 2 is less than 4 AND if 10 is less than 5 AND if 5 subtract from 10 is 0, it returns a result. As one condition (10 – 5 = 0) is false, it will return FALSE. The second custom message in the MsgBox dialog box will be returned.

If you run this code, it will return “Some conditions are true”, which is the second custom message in the MsgBox.

Read More: Excel VBA: Combining If with And for Multiple Conditions

Method 3 – Using a VBA IF Statement with Multiple Conditions: IF with AND, OR Together

• Consider the following macro.

Sub IfWithAndOr()
If 2 < 4 And 10 > 5 Or 10 - 5 = 0 Then
    MsgBox "All conditions are true"
Else
    MsgBox "Some conditions are true"
End If
End Sub

In the second line of the code, three conditions were inserted in the IF statement with the AND and OR functions. The macro line If 2 < 4 And 10 > 5 Or 10 – 5 = 0 Then means, if 2 is less than 4 AND if 10 is less than 5 Or if 5 subtract from 10 is 0 , it returns a result. As the condition with the OR function (10 – 5 = 0) here is false, it will return TRUE. The first custom message in the MsgBox dialog box will be returned.

If you run this code, it will return “All conditions are true”, which is the first custom message in the MsgBox.

Method 4 – Using a VBA IF Statement with Multiple Conditions: ElseIF Statement

• Consider the following macro.

Sub IfWithElseIf()
Dim ExamMarks As Integer
ExamMarks = 65
If ExamMarks >= 80 Then
    MsgBox "Well done!"
ElseIf ExamMarks >= 60 And ExamMarks < 80 Then
    MsgBox "Keep it up!"
ElseIf ExamMarks >= 40 And ExamMarks < 60 Then
    MsgBox "Needs improvement!"
Else
    MsgBox "Fail!"
End If
End Sub

This piece of code refers to,

• if a student gets marks equal to or above 80 in the exam, the system will display a “Well done!” message.
• If he gets marks equal to or above 60 but less than 80, the system will return a “Keep it up!” message.
• If the student gets marks equal to or above 40 but less than 60, he will get a “Needs improvement!” message.
• For other marks, the system will return a “Fail!” message.

The exam mark 65 is stored in the ExamMarks variable. After running the code, the “Keep it up!” message will be displayed.

If you run this code, Excel will return  “Keep it up!” in the MsgBox.

Read More: Excel VBA: If Cell Contains Value Then Return a Specified Output

Method 5 – Using a VBA IF Statement with Multiple Conditions: Nested IF Function

• Consider the following macro.

Sub IfCondNestedIf()
Dim iNum As Integer
iNum = 5
If iNum > 0 Then
    MsgBox "Positive Number"
Else
    If iNum < 0 Then
        MsgBox "Negative Number"
Else
    MsgBox "Number is Zero"
    End If
End If
End Sub

In this code,  number 5 is stored in the iNum variable. A test checks whether that number is positive, negative or zero with the Nested IF function. If iNum is greater than 0, the number is positive; If iNum is less than 0, the number is negative; otherwise, the number is zero.

If you run this code, you will get “Positive Number” as the output, since number 5 is positive.

B5 contains number 5. To perform the Nested IF operation and find whether this number is positive, negative or zero and return the result in C5:

VBA Code:

Sub IfCondNestedIfRange()
If Range("B5").Value > 0 Then
    Range("C5").Value = "Positive Number"
Else
    If Range("B5").Value < 0 Then
        Range("C5").Value = "Negative Number"
Else
    Range("B5").Value = "Zero"
    End If
End If
End Sub

If you run this code, you will get “Positive Number” as the output in C5.

Read More: Excel VBA Nested If Then Else in a For Next Loop

Method 6 – Using a VBA IF Statement with Multiple Conditions: Multiple IF…Then Statements

• Consider the following macro.

Sub MultipleIfThen()
Dim ExamMarks As Integer
ExamMarks = 65
If ExamMarks >= 80 Then
    MsgBox "Well done!"
End If
If ExamMarks >= 60 And ExamMarks < 80 Then
    MsgBox "Keep it up!"
End If
If ExamMarks >= 40 And ExamMarks < 60 Then
    MsgBox "Needs improvement!"
End If
If ExamMarks < 40 Then
    MsgBox "Fail!"
End If
End Sub

This piece of code:

• tests the first condition – if any student gets marks equal to or above 80 in the exam, the system will display a “Well done!” message. If this condition is not valid, the code moves to the next IF condition.
• tests the second condition – if he gets marks equal to or above 60 but less than 80, the system will return a “Keep it up!” message. If this condition is not valid, the code moves  to the next IF condition.
• tests the third condition – if the student gets marks  equal to or above 40 but less than 60, he will get a “Needs improvement!” message. If this condition is not valid, the code moves to the next IF condition.
• tests the last condition – if he gets none of the marks above, the system will return a “Fail!” message. If this condition is not valid, the code ends the procedure.

65 is stored in the ExamMarks variable. After running this code, the “Keep it up!” message will be displayed.

A return message “Keep it up!” is displayed in the MsgBox.

Method 7 – Using a VBA IF Statement with Multiple Conditions: IF with FOR Loop

• Consider the following macro.

Sub IfWithForLoop()
Dim iValue As Integer
Dim i As Integer
Dim iEven As Integer
Dim iOdd As Integer
For i = 1 To 10
iValue = InputBox("Enter Numbers from 1 to 10")
    If iValue Mod 2 = 0 Then
    iEven = iEven + i
    Else
    iOdd = iOdd + i
    End If
Next i
MsgBox "Sum of all odd numbers entered " & iEven
MsgBox "Sum of all even numbers entered " & iOdd
End Sub

This code returns the sum value of the Even numbers and the Odd numbers between 1 to 10.

Numbers from 1 to 10  are entered in the input box.  If after being divided by 2, the remainder of the values is 0, the numbers are declared even numbers, and the code calculates their sum in the IF statement. The rest of the values inserted will fall under the odd number category and will be summed in the Else statement.

After running the code, an input box is displayed:

After the number insertion, it shows the summation of all odd numbers.

It will show the summation of all even numbers.

Method 8 – Using a Macro IF Statement with Multiple Conditions: IF with IsEmpty, Not and IsNumeric Together

• Right-click the worksheet and select View Code.

• Enter the following code in the code window.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrHandler
Application.EnableEvents = False
If IsEmpty(Target) Then
Application.EnableEvents = True
Exit Sub
End If
If Not Intersect(Target, Range("B1:B20")) Is Nothing Then
If IsNumeric(Target) Then
Target.Interior.Color = RGB(255, 255, 0)
End If
End If
Application.EnableEvents = True
ErrHandler:
    Application.EnableEvents = True
    Exit Sub
End Sub

• Save the code.
• Go back to the worksheet and enter numbers in B1:B10.

The cell background color automatically changes. If you enter letters, the color won’t change. If you enter numbers in any other range, the color won’t change.

Download Workbook

Download the free practice Excel workbook here.

Related Articles

• Excel VBA to Check If String Contains Letters
• Else Without If Error VBA in Excel
• Excel VBA: Check If a Sheet Exists