6 Examples to Understand VBA Equal and Not Equal to Operators
Equal to and Not Equal to are comparison operators in VBA. Used to check or compare values, let us take a look with examples one by one.
What is equal to and not equal to operators in VBA?
- Equal to and Not Equal to are comparison operators in VBA.
- These are used to check or compare values.
- Let us take a look at examples one by one.
What is Not Equal to Operator in VBA?
In VBA, Not Equal to operator is used as:
<>
For example:
a <> b
If a is not equal to b then it returns True otherwise False. Let us see this in examples.
A simple example of checking two variables by not equal to
- We declared two integer type variable
- Assigned values and checked in the If statement
Code:
Sub not_equal_ex() Dim a As Integer Dim b As Integer a = 10 b = 20 If a <> b Then MsgBox "a and b are not equal!" Else MsgBox "a and b are equal!" End If End Sub
Result:
As a and b are not equal, it returned True, which is why the message box inside If statement is displayed.
If both values are equal then the Else statement should have executed as shown below:
Sub not_equal_ex() Dim a As Integer Dim b As Integer a = 10 b = 10 If a <> b Then MsgBox "a and b are not equal!" Else MsgBox "a and b are equal!" End If End Sub
Output:
As the condition is False i.e. a is equal to b (while we were testing if a is not equal to b), so Else statement is executed.
What is equal to operator
The “Equal to” is also a comparison operator used by this sign “=”. For example:
a = b
It returns True if both values are equal, otherwise, False. So, it’s opposite to the “<>” operator.
An example of Equal to (=) operator
Again we have two variables and test their values for equality.
If both are equal then it should return True and If statement should execute.
If False, Else statement should execute.
Code:
Sub equal_ex() Dim a As Integer Dim b As Integer a = 5 b = 5 'Checking if both variables are equal If a = b Then MsgBox "a and b are equal!" Else MsgBox "a and b are not equal!" End If End Sub
Result:
As a and b are equal, th If returned True and the statement inside executed.
Now, as we assign different values:
Sub equal_ex() Dim a As Integer Dim b As Integer a = 50 b = 100 'Checking if both variables are equal If a = b Then MsgBox "a and b are equal!" Else MsgBox "a and b are not equal!" End If End Sub
Result:
An example of not equal with Excel sheet cells
In this example, we will check the equality of cells text.
If A2 cell value is not equal to B2 then set the Interior/background color of C2 to red, otherwise, green.
See the code and sheet with the result:
Sub equality_ex() If Range("A1") <> Range("B1") Then Range("C1").Interior.Color = vbRed Else Range("C1").Interior.Color = vbGreen End If End Sub
Result:
As A2 cell is not equal to B2 so the color is set as Red.
Checking Range values with Equal to operator
For this example, we compare the values of a Range of cells by using the equal to operator.
- For that, we created a range of A to A10 cells.
- In For..Each loop, we iterated through each cell of the range.
- In each iteration, the value of A(x) cell is compared to the B(x) cell with “=” operator.
- If the value matches (return result True), we will fill the respective C(x) color to Red and its border color to White.
- If False (does not match), we will change the interior/fill color to Yellow with Cyan border.
Have a look:
Sub equality_ex() Dim rng As Range Dim x Set rng = Range("A1:A10") x = 1 For Each Cell In rng If Cell <> Range("B" & x) Then Range("C" & x).Interior.Color = vbRed Range("C" & x).Borders.Color = vbWhite Else Range("C" & x).Interior.Color = vbYellow Range("C" & x).Borders.Color = vbCyan End If x = x + 1 Next End Sub
Result: