VBA IF Statement with Multiple Conditions in Excel (8 Methods)

When you want to extract results from so much data based on a certain condition, then Excel’s IF statement is a very efficient way to get that. Implementing VBA is the most effective, quickest and safest method to run any operation in Excel. This article will show you 8 different methods of the VBA IF statement with multiple conditions in Excel.

VBA IF Statement with Multiple Conditions in Excel: 8 Methods

Following this section, you will learn how you can utilize VBA IF statement with multiple conditions in Excel in 8 different methods.

1. VBA IF Statement with Multiple Conditions: IF with OR

The OR Function is Excel’s built-in logical function that evaluates two or more conditions. It returns TRUE if at least one of the conditions is true, and it returns FALSE if all of the conditions are false.

Example with VBA Code:

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, we inserted two conditions 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 then return certain result. Now, as one condition (5 < 10) here is true and the other condition (10 < 5) is false, according to the definition of the OR function, it will return TRUE, hence, the first custom message from the MsgBox dialogue box will be returned in our case.

Now, if you run this code, you will get the return result of “One true condition exists!”, which is the first custom message in the MsgBox.

To get the return result as FALSE, now let’s change the conditions in the IF statement; let’s provide all false conditions in there.

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 this piece of the code, we inserted two conditions 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 then return certain result. Now, as both of the conditions are false, according to the definition of the OR function, it will return FALSE, hence, the second custom message from the MsgBox dialogue box will be returned in our case.

Now, if you run this code, you will get the return result of “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 value in your Excel worksheet. All you have to do is, just write the sheet’s code name, followed by the range whose value you want to measure and then insert the condition. For instance, if you have a numeric value in Cell B5 and you want to know if the value is less than or greater than 10, then write the macro line like this,

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

Here,

Sheet1 = code name of the worksheet

Example with VBA Code:

So, the full code becomes,

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

If you run this code, then if the value in cell B5 is less than 10 – which is true in our case, as you can see from the image below that cell B5 holds 5 – then it will return the custom message “Value in cell B5 is less than 10” from the Excel MsgBox dialogue box.

But if the cell carries a value which is greater than 10 – cell B7 carries 15 which is greater than 10 – then it will return the other message.

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

If you run this code, then it will return the custom message “Value in cell B7 is greater than 10” from the Excel MsgBox dialogue box.

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

2. VBA IF Statement with Multiple Conditions: IF with AND

The AND Function is Excel’s built-in logical function that evaluates two or more conditions. It returns TRUE if all of the conditions are true, and it returns FALSE if at least one condition is false.

Example with VBA Code:

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, we inserted two conditions 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 then returns a certain result. Now, as both of the conditions are true, according to the definition of the AND function, it will return TRUE, hence, the first custom message from the MsgBox dialogue box will be returned in our case.

Now, if you run this code, you will get the return result of “Both conditions are true!”, which is the first custom message in the MsgBox.

To get the return result as FALSE, now let’s change the conditions in the IF statement; let’s provide at least one false condition in there.

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, we inserted two conditions 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 then return certain result. Now, as one condition (2 < 4) here is true and the other condition (10 < 5) is false, according to the definition of the AND function, it will return FALSE, hence, the second custom message from the MsgBox dialogue box will be returned in our case.

Now, if you run this code, you will get the return result of “Only one condition is true!”, which is the second custom message in the MsgBox.

IF with Multiple AND in Excel

You can perform as many AND as you want with the IF. But you will get TRUE only when all of the conditions are true. Even if only one condition is false among multiple ANDs, you will get the FALSE return value.

Example with VBA Code:

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, we inserted three conditions 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 then return certain result. Now, as all of the conditions are true, according to the definition of the AND function, it will return TRUE, hence, the first custom message from the MsgBox dialogue box will be returned in our case.

Now, if you run this code, you will get the return result of “All conditions are true”, which is the first custom message in the MsgBox.

To get the return result as FALSE, now let’s change the conditions in the IF statement; let’s provide at least one false condition in there.

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, we inserted three conditions 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 then return certain result. Now, as one condition (10 – 5 = 0) here is false among the other true conditions, according to the definition of the AND function, it will return FALSE, hence, the second custom message from the MsgBox dialogue box will be returned in our case.

Now, if you run this code, you will get the return result of “Some conditions are true”, which is the second custom message in the MsgBox.

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

3. VBA IF Statement with Multiple Conditions: IF with AND, OR Together

You can perform both AND and OR functions together with the IF condition in VBA. If all conditions that are joined with the AND function are true and if the conditions with the OR function are false, then it will return TRUE. Otherwise, if any conditions with the AND function are false, then it will return FALSE as the return value.

Example with VBA Code:

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, we inserted three conditions 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 then return certain result. Now, the condition with the OR function (10 – 5 = 0) here is false among the other true conditions with the AND function, according to the definition of the AND and OR functions together, it will return TRUE, hence, the first custom message from the MsgBox dialogue box will be returned in our case.

Now, if you run this code, you will get the return result of “All conditions are true”, which is the first custom message in the MsgBox.

On the other hand, if the conditions with the AND function held at least one false condition, then the return result would have been otherwise.

4. VBA IF Statement with Multiple Conditions: ElseIF Statement

If you have multiple conditions and you want to extract the result by testing all the conditions with the IF statement, then you can utilize the ElseIF statement in VBA Excel.

In the following section, we will show you an example with a VBA code.

Example with VBA Code:

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 any student gets marks equal to or above 80 in his exam, then the system will throw a “Well done!” message.
• If he gets marks from equal to or above 60 to less than 80, then the system will return a “Keep it up!” message.
• If the student gets marks from equal to or above 40 to less than 60, then he will get a “Needs improvement!” message.
• And if he gets nothing from these marks, then the system will return a “Fail!” message to him.

Now, we stored the exam mark 65 in the ExamMarks variable. So, after executing this code, we will get the “Keep it up!” message.

Now, if you run this code, Excel will throw you a return message of “Keep it up!” in the MsgBox.

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

5. VBA IF Statement with Multiple Conditions: Nested IF Function

The Nested IF function is a function where you can insert multiple IF statements to test all the conditions given. Nested IF is essential to analyze complex data with multiple conditions.

In the following section, we will show you an example with a VBA code.

Example with VBA Code:

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, first, we stored the number 5 in the iNum variable. Then we tested whether that number is positive or negative or zero with the Nested IF function. If iNum is greater than 0, then the number is positive; If iNum is less than 0, then the number is negative; otherwise, the number is zero.

If you run this code, then you will get “Positive Number” as the output since the number 5, which we tested against every condition is positive.

You can also perform Nested IF or any other conditions that we have been showing you for the value that you have in your Excel worksheet.

For instance, we have number 5 in cell B5 in our spreadsheet. Now we will perform the Nested IF operation to find out whether this number is positive or negative or zero and throw the result in cell C5.

VBA Code:

The VBA code to perform the task is given below.

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, then you will get “Positive Number” as the output in Cell C5 since the number 5, which we tested against every condition is positive.

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

6. VBA IF Statement with Multiple Conditions: Multiple IF…Then Statements

When you want to test a condition to check whether the result is TRUE or FALSE and then based on the result execute one set of instructions, then multiple IF-Then statements should be implemented in those cases.

In the following section, we will show you an example with a VBA code.

Example with VBA Code:

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 refers to,

• First, it tests the first condition – if any student gets marks equal to or above 80 in his exam, then the system will throw a “Well done!” message. If this condition is not valid, then leave this condition and go to the next IF condition.
• Second, it tests the second condition – if he gets marks from equal to or above 60 to less than 80, then the system will return a “Keep it up!” message. If this condition is not valid, then leave this condition and go to the next IF condition.
• Third, it tests the third condition – if the student gets marks from equal to or above 40 to less than 60, then he will get a “Needs improvement!” message. If this condition is not valid, then leave this condition and go to the next IF condition.
• And finally, it tests the last condition – if he gets nothing from these marks, then the system will return a “Fail!” message to him. If this condition is not valid, then leave this condition and end the procedure.

Now, we stored the exam mark 65 in the ExamMarks variable. So, after executing this code, we will get the “Keep it up!” message.

Now, if you run this code, Excel will throw you a return message of “Keep it up!” in the MsgBox.

7. VBA IF Statement with Multiple Conditions: IF with FOR Loop

Iteration is a great way to execute the IF statement with multiple conditions. This section will show you how to perform the IF statement with the FOR Loop for multiple conditions in Excel VBA.

Example with VBA Code:

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 will give you the sum value of the Even numbers and the Odd numbers between 1 to 10.

This code will first ask you to insert numbers from 1 to 10 in the input box. After number insertion, if after dividing by 2 the remainder of the values is 0 then they are even numbers, and this code will calculate the sum of them only in the IF statement. On the other hand, the rest of the values inserted will fall under the odd number category and they will also be summed up in the Else statement.

As we told you before, after executing the code, there will be a pop-up input box, asking you for numbers from 1 to 10.

After the number insertion, it will show you the summation of all the odd numbers inserted.

Then it will show you the summation of all the even numbers inserted.

8. Macro IF Statement with Multiple Conditions: IF with IsEmpty, Not and IsNumeric Together

IF statement can also be executed with other functions such as IsEmpty, NOT, IsNumeric etc. This section will show you how you can perform the IF statement with multiple conditions with all of those functions in VBA Excel.

In the following dataset we will perform the IF statement with the IsEmpty, Not and IsNumeric functions in a way that whenever we insert a number in the range B1:B10, the background colour will be automatically changed.

• First, right-click on the worksheet and select View Code. It will take you to the code window.

• Now, copy the following code and paste it into 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 this code.
• Now if you go back to the worksheet and start inserting numbers in the range B1:B10, you will see that the cell background colour is automatically changing. If you insert letters, then the colour won’t be changed. Or even if you insert numbers in any other range except B1:B10, then still the colour won’t change.

Only when you insert numbers in the range B1:B10, then the background color will be changed because we have performed an IF statement along with multiple functions.

Download Workbook

You can download the free practice Excel workbook from here.

Conclusion

To conclude, this article showed you 8 different methods of the VBA IF statement with multiple conditions in Excel. I hope this article has been very beneficial to you. Feel free to ask any questions regarding the topic.

Related Articles

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