Summary Operator Is, Like, Mod and Not in Excel VBA

Even though Operator Is, Like, Mod and Not totally different in comparison but sometime difficult to remember which one need to use. Here comparison table for reference.

No.	Operator	Logical
1	Is	Compare two object reference variables.
2	Like	Compare two strings.
3	Mod	Divide two numbers and return only the remainder.
4	Not	Perform logical negation on an expression.

Operator		Commonly Use for
Is		ObjectA Is ObjectB = TRUE/FALSE
Is		MyRange Is Nothing = TRUE/FALSE
Is		Intersect(MyRange,Selection) Is Nothing = TRUE/FALSE
Like		String Like String Pattern(*,#,?,[!]) = TRUE/FALSE
Mod		A=7,B=2, A Mod B = 1 (Balance for 2*3=6)
Not		A=7,B=2. Not (A > B) = FALSE, Not (A < B) = TRUE

VBA Vode:

Option Explicit
Sub OperatorIsLikeModNot()
    
    Dim MySheet As Worksheet
    Dim iNumA As Integer, iNumB As Integer
    Dim StrUrlA As String, StrUrlB As String
    
    Dim MyRange As Range, MySelect As Range
    
    Set MySheet = ActiveSheet
    Set MyRange = MySheet.Range("A1:D20")
    Set MySelect = Selection
    StrUrlA = "https://mrvba.blogspot.com/"
    StrUrlB = "C:\Users\UserName\Desktop\AlQuran MP3"
    iNumA = 10: iNumB = 3
    
    'Combination Is and Not Operator
    If Not MySheet Is Nothing Then
        Debug.Print MySheet.Name
        'Combination Is Intersect and Not Operator
        If Not Intersect(MySelect, MyRange) Is Nothing Then
            Debug.Print "Your Selected cell is in range A1 to D20."
        Else
            Debug.Print "Your Selected cell is Not in range."
        End If
        'Operator Like
        If StrUrlA Like "https://*" Then
            Debug.Print "Yes! This is URL:" & StrUrlA
        Else
            Debug.Print "This is not Url:" & StrUrlB
        End If
        If StrUrlB Like "https://*" Then
            Debug.Print "Yes! This is URL:" & StrUrlA
        Else
            Debug.Print "This is not Url:" & StrUrlB
        End If
        'Operator Mode and And operator
        If IsNumeric(iNumA) And IsNumeric(iNumB) Then
            Debug.Print iNumA Mod iNumB
        End If
        'Operator Not
        If Not (iNumA > iNumB) Then
            Debug.Print "No: " & iNumA & " is Less than " & iNumB
        ElseIf Not (iNumA < iNumB) Then
            Debug.Print "Yes: " & iNumA & " is more than " & iNumB
        End If
    End If

End Sub

Note:
Most commonly use Operator among these is Not and Is, sometime only use like and seldom use Mod.

Microsoft Reference-Is-operator
Microsoft Reference-Like-operator
Microsoft Reference-Mod-operator
Microsoft Reference-Mod-operator example
Microsoft Reference-Not-operator

Practice makes perfect. Thank You.

excel training beginners
coding in vba
excel training online
visual basic for applications

The different between Operator Or, Xor, And, Eqv and Imp in Excel VBA

Sometime very hard to choose which Operator need use when we have 2 expressions or statements to compare because the result almost the same. Here we have table for reference.

No.			Operator			Logical
1			Or			Disjunction
2			Xor			Exclusion
3			And			Conjunction
4			Eqv			Equivalence
5			Imp			Implication

			Operator
No.	If expression1 is	And expression2 is	Or	Xor	And	Eqv	Imp
1	True	True	True	False	True	True	True
2	True	False	True	True	False	False	False
3	True	Null	True	Null	Null	Null	Null
4	False	True	True	True	False	False	True
5	False	False	False	False	False	True	True
6	False	Null	Null	Null	False	Null	True
7	Null	True	True	Null	Null	Null	True
8	Null	False	Null	Null	False	Null	Null
9	Null	Null	Null	Null	Null	Null	Null

For more example refer below table and code.

VBA Vode:

Option Explicit
Sub ComparisonOperator_All()

    Dim i As Integer
    Dim myRng As Range, Cell As Range
    Dim iNum1, INum2, INum3, INum4
    With ActiveSheet
        i = 2
        Do
            If .Range("B" & i) = "" Then
                iNum1 = Null
            Else
                iNum1 = .Range("B" & i)
            End If
            If .Range("C" & i) = "" Then
                INum2 = Null
            Else
                INum2 = .Range("C" & i)
            End If
            If .Range("E" & i) = "" Then
                INum3 = Null
            Else
                INum3 = .Range("E" & i)
            End If
            If .Range("F" & i) = "" Then
                INum4 = Null
            Else
                INum4 = .Range("F" & i)
            End If
            
            .Range("D" & i) = iNum1 > INum2
            .Range("G" & i) = INum3 > INum4
            
            'Operator
            .Range("H" & i) = iNum1 > INum2 Or INum3 > INum4
            .Range("I" & i) = iNum1 > INum2 Xor INum3 > INum4
            .Range("J" & i) = iNum1 > INum2 And INum3 > INum4
            .Range("K" & i) = iNum1 > INum2 Eqv INum3 > INum4
            .Range("L" & i) = iNum1 > INum2 Imp INum3 > INum4
            
            i = i + 1
        Loop While .Range("A" & i) <> ""
    
        'To put background color base on result
        Set myRng = .Range("H2:L10")
        For Each Cell In myRng
            If Cell.Text = "TRUE" Then
                Cell.Interior.ColorIndex = 4 'Green
            ElseIf Cell.Text = "FALSE" Then
                Cell.Interior.ColorIndex = 6 'Yellow
            Else
                Cell.Interior.ColorIndex = 3 'Red
            End If
        Next
    End With
    
End Sub

Note:
The above code can be simplify with array in advanced topic. These operator commonly use with If...Then..Else function. Operator Or & And are the most commonly use.

Microsoft Reference-Or-operator
Microsoft Reference-Xor-operator
Microsoft Reference-And-operator
Microsoft Reference-Eqv-operator
Microsoft Reference-Imp-operator

Practice makes perfect. Thank You.

excelmacros
macro excel
excel programming
excel vba

Imp Operator in Excel VBA

Operator Imp is Used to perform a logical implication on two expressions or statements. For example we have 2 sets of number to compare 20<30 (True), another 30<40 (True). Therefore if we used 20<30 Imp 30<40 then the result is True and both False result is True. If both Null then result is Null. The details combination of Imp logical test as below.

No.

If expression1 is

And expression2 is

The result is

1

True

True

True

2

True

False

False

3

True

Null

Null

4

False

True

True

5

False

False

True

6

False

Null

True

7

Null

True

True

8

Null

False

Null

9

Null

Null

Null

For more example refer below table and code.

VBA Vode:

Option Explicit
Sub ComparisonOperator_Imp()

    Dim i As Integer
    Dim myRng As Range, Cell As Range
    Dim iNum1, INum2, INum3, INum4
    With ActiveSheet
        i = 2
        Do
            If .Range("B" & i) = "" Then
                iNum1 = Null
            Else
                iNum1 = .Range("B" & i)
            End If
            If .Range("C" & i) = "" Then
                INum2 = Null
            Else
                INum2 = .Range("C" & i)
            End If
            If .Range("E" & i) = "" Then
                INum3 = Null
            Else
                INum3 = .Range("E" & i)
            End If
            If .Range("F" & i) = "" Then
                INum4 = Null
            Else
                INum4 = .Range("F" & i)
            End If
            
            .Range("D" & i) = iNum1 > INum2
            .Range("G" & i) = INum3 > INum4
            
            'Or operator
            .Range("H" & i) = iNum1 > INum2 Imp INum3 > INum4
            
            i = i + 1
        Loop While .Range("A" & i) <> ""
    
        'To put background color base on result
        Set myRng = .Range("H2:H10")
        For Each Cell In myRng
            If Cell.Text = "TRUE" Then
                Cell.Interior.ColorIndex = 4 'Green
            ElseIf Cell.Text = "FALSE" Then
                Cell.Interior.ColorIndex = 6 'Yellow
            Else
                Cell.Interior.ColorIndex = 3 'Red
            End If
        Next
    End With
    
End Sub

Note:
The above code can be simplify with array in advanced topic. This Imp operator commonly use with If...Then..Else function.

Microsoft Reference-Imp-operator

Practice makes perfect. Thank You.

excelmacros
macro excel
excel programming
excel vba

Eqv Operator in Excel VBA

Operator Eqv is Used to perform a logical equivalence on two expressions or statements. For example we have 2 sets of number to compare 20<30 (True), another 30<40 (True). Therefore if we used 20<30 Eqv 30<40 then the result is True and both False result is True. If both or either one is Null then result is Null. The details combination of Eqv logical test as below.

No.

If expression1 is

And expression2 is

The result is

1

True

True

True

2

True

False

False

3

False

True

False

4

False

False

True

5

Null

True

Null

6

False

Null

Null

7

Null

Null

Null

For more example refer below table and code.

VBA Vode:

Option Explicit
Sub ComparisonOperator_Eqv()

    Dim i As Integer
    Dim myRng As Range, Cell As Range
    Dim iNum1, INum2, INum3, INum4
    With ActiveSheet
        i = 2
        Do
            If .Range("B" & i) = "" Then
                iNum1 = Null
            Else
                iNum1 = .Range("B" & i)
            End If
            If .Range("C" & i) = "" Then
                INum2 = Null
            Else
                INum2 = .Range("C" & i)
            End If
            If .Range("E" & i) = "" Then
                INum3 = Null
            Else
                INum3 = .Range("E" & i)
            End If
            If .Range("F" & i) = "" Then
                INum4 = Null
            Else
                INum4 = .Range("F" & i)
            End If
            
            .Range("D" & i) = iNum1 > INum2
            .Range("G" & i) = INum3 > INum4
            
            'Or operator
            .Range("H" & i) = iNum1 > INum2 Eqv INum3 > INum4
            
            i = i + 1
        Loop While .Range("A" & i) <> ""
    
        'To put background color base on result
        Set myRng = .Range("H2:H8")
        For Each Cell In myRng
            If Cell.Text = "TRUE" Then
                Cell.Interior.ColorIndex = 4 'Green
            ElseIf Cell.Text = "FALSE" Then
                Cell.Interior.ColorIndex = 6 'Yellow
            Else
                Cell.Interior.ColorIndex = 3 'Red
            End If
        Next
    End With
    
End Sub

Note:
The above code can be simplify with array in advanced topic. This Eqv operator commonly use with If...Then..Else function.

Microsoft Reference-Eqv-operator

Practice makes perfect. Thank You.

macro enabled excel
excel macro
vba coding
vba code

And Operator in Excel VBA

Operator And Used to perform a logical conjunction on two expressions or statements. For example we have 2 sets of number to compare 20<30 (True), another 30<40 (True). Therefore if we used 20<30 And 30<40 then the result is True and vice versa. The details combination of And logical test as below.

No.

If expression1 is

And expression2 is

The result is

1

True

True

True

2

True

False

False

3

True

Null

Null

4

False

True

False

5

False

False

False

6

False

Null

False

7

Null

True

Null

8

Null

False

False

9

Null

Null

Null

For more example refer below table and code.

VBA Vode:

Option Explicit
Sub ComparisonOperator_And()

    Dim i As Integer
    Dim myRng As Range, Cell As Range
    Dim iNum1, INum2, INum3, INum4
    With ActiveSheet
        i = 2
        Do
            If .Range("B" & i) = "" Then
                iNum1 = Null
            Else
                iNum1 = .Range("B" & i)
            End If
            If .Range("C" & i) = "" Then
                INum2 = Null
            Else
                INum2 = .Range("C" & i)
            End If
            If .Range("E" & i) = "" Then
                INum3 = Null
            Else
                INum3 = .Range("E" & i)
            End If
            If .Range("F" & i) = "" Then
                INum4 = Null
            Else
                INum4 = .Range("F" & i)
            End If
            
            .Range("D" & i) = iNum1 > INum2
            .Range("G" & i) = INum3 > INum4
            
            'Or operator
            .Range("H" & i) = iNum1 > INum2 And INum3 > INum4
            
            i = i + 1
        Loop While .Range("A" & i) <> ""
    
        'To put background color base on result
        Set myRng = .Range("H2:H10")
        For Each Cell In myRng
            If Cell.Text = "TRUE" Then
                Cell.Interior.ColorIndex = 4 'Green
            ElseIf Cell.Text = "FALSE" Then
                Cell.Interior.ColorIndex = 6 'Yellow
            Else
                Cell.Interior.ColorIndex = 3 'Red
            End If
        Next
    End With
    
End Sub

Note:
The above code can be simplify with array in advanced topic. This And operator commonly use with If...Then..Else function.

Microsoft Reference-And-operator

Practice makes perfect. Thank You.

excelmacros
macro excel
excel programming
excel vba

Xor Operator in Excel VBA

Operator Xor is used to perform a logical exclusion on two expressions or statements most likely reverse with Or operator. For example we have 2 sets of number to compare 20<30 (True), another 30<40 (True). Therefore if we used 20<30 Xor 30<40 then the result is False and vice versa. The details combination of Xor logical test as below.

No.

If expression1 is

And expression2 is

Then result is

1

True

True

False

2

True

False

True

3

False

True

True

4

False

False

False

For more example refer below table and code.

VBA Vode:

Option Explicit
Sub ComparisonOperator_Xor()

    Dim i As Integer
    Dim myRng As Range, Cell As Range
    Dim iNum1, INum2, INum3, INum4
    With ActiveSheet
        i = 2
        Do
            If .Range("B" & i) = "" Then
                iNum1 = Null
            Else
                iNum1 = .Range("B" & i)
            End If
            If .Range("C" & i) = "" Then
                INum2 = Null
            Else
                INum2 = .Range("C" & i)
            End If
            If .Range("E" & i) = "" Then
                INum3 = Null
            Else
                INum3 = .Range("E" & i)
            End If
            If .Range("F" & i) = "" Then
                INum4 = Null
            Else
                INum4 = .Range("F" & i)
            End If
            
            .Range("D" & i) = iNum1 > INum2
            .Range("G" & i) = INum3 > INum4
            
            'Or operator
            .Range("H" & i) = iNum1 > INum2 Xor INum3 > INum4
            
            i = i + 1
        Loop While .Range("A" & i) <> ""
    
        'To put background color base on result
        Set myRng = .Range("H2:H6")
        For Each Cell In myRng
            If Cell.Text = "TRUE" Then
                Cell.Interior.ColorIndex = 4 'Green
            ElseIf Cell.Text = "FALSE" Then
                Cell.Interior.ColorIndex = 6 'Yellow
            Else
                Cell.Interior.ColorIndex = 3 'Red
            End If
        Next
    End With
    
End Sub

Note:
The above code can be simplify with array in advanced topic. This Xor operator commonly use with If...Then..Else function.

Microsoft Reference-Xor-operator

Practice makes perfect. Thank You.

excel training beginners
coding in vba
excel training online
visual basic for applications

Or Operator In Excel VBA

 Operator Or is used to perform a logical dis junction on two expressions or statements. For example we have 2 sets of number to compare 20<30 (True), another 30<40 (True). Therefore if we used 20<30 Or 30<40 then the result is True and vice versa. The details combination of Or logical test as below.

No.

If expression1 is

And expression2 is

Then result is

1

True

True

True

2

True

False

True

3

True

Null

True

4

False

True

True

5

False

False

False

6

False

Null

Null

7

Null

True

True

8

Null

False

Null

9

Null

Null

Null

For more example refer below table and code.

VBA Code:

Option Explicit
Sub ComparisonOperator_Or()

    Dim i As Integer
    Dim myRng As Range, Cell As Range
    Dim iNum1, INum2, INum3, INum4
    With ActiveSheet
        i = 2
        Do
            If .Range("B" & i) = "" Then
                iNum1 = Null
            Else
                iNum1 = .Range("B" & i)
            End If
            If .Range("C" & i) = "" Then
                INum2 = Null
            Else
                INum2 = .Range("C" & i)
            End If
            If .Range("E" & i) = "" Then
                INum3 = Null
            Else
                INum3 = .Range("E" & i)
            End If
            If .Range("F" & i) = "" Then
                INum4 = Null
            Else
                INum4 = .Range("F" & i)
            End If
            
            .Range("D" & i) = iNum1 < INum2
            .Range("G" & i) = INum3 < INum4
            
            'Or operator
            .Range("H" & i) = iNum1 < INum2 Or INum3 < INum4
            
            i = i + 1
        Loop While .Range("A" & i) <> ""
    
        'To put background color base on result
        Set myRng = .Range("H2:H10")
        For Each Cell In myRng
            If Cell.Text = "TRUE" Then
                Cell.Interior.ColorIndex = 4 'Green
            ElseIf Cell.Text = "FALSE" Then
                Cell.Interior.ColorIndex = 6 'Yellow
            Else
                Cell.Interior.ColorIndex = 3 'Red
            End If
        Next
    End With
    
End Sub

Note:
The above code can be simplify with array in advanced topic. This Or operator commonly use with If...Then..Else function.

Microsoft Reference-Or-operator

Practice makes perfect. Thank You.

Not Operator in Excel VBA

Operator NOT is to test any expression or logical statement in reverse way. For example 20 < 30 (20 less than 30) this statement is TRUE but if we put NOT (20<30) then the result will be FALSE. For example as per below table.

No.			If expression is			Then result is
1			True			False
2			False			True
3			Null			Null

 VBA Code:
 

Option Explicit
Sub ComparisonOperator_Not()
    
    Dim i As Integer, j As Integer
     
    With ActiveSheet
        i = 2
        Do
            .Range("D" & i) = Not (.Range("B" & i) < .Range("C" & i))
            .Range("E" & i) = Not (.Range("B" & i) > .Range("C" & i))
            .Range("F" & i) = Not (.Range("B" & i) = .Range("C" & i))
            
            For j = 1 To 3
                If .Cells(i, 3 + j).Text = "TRUE" Then
                    .Cells(i, 3 + j).Interior.ColorIndex = 4
                Else
                    .Cells(i, 3 + j).Interior.ColorIndex = 6
                End If
            Next j
            i = i + 1
        Loop While .Range("A" & i) <> ""
    End With
    
End Sub

But most commonly use for NOT is to check either any variables IsNumeric or Not as per below example.

VBA Code:

Option Explicit
Sub ComparisonOperator_NotNumeric()
    
    Dim A As Integer 'Changed this
    Dim B As String 'Changed this
    
    A = 30 'Changed this
    B = "Name" 'Changed this
    
    If Not IsNumeric(A) And Not IsNumeric(B) Then
        MsgBox "Both A and B is String."
    ElseIf IsNumeric(A) And Not IsNumeric(B) Then
        MsgBox "A is Number and B is String."
    ElseIf Not IsNumeric(A) And IsNumeric(B) Then
        MsgBox "A is String and B is Number."
    Else
        MsgBox "Both A and B is Number."
    End If

End Sub

Microsoft Reference-Not-operator

Practice makes perfect. Thank You.

Mod Operator in Excel VBA

Operator Mod is Used to divide two numbers and return only the remainder after round.

• If number 10/2 = 5 with no balance, so 10 Mod 2 = 0.
• If number 10/3 =  3.33 is equivalent to 9/3 =3 with balance 1, so 10 Mod 3 = 1

Please refer below example for more detail.

VBA Code:

Option Explicit
Sub ComparisonOperator_Mod()

    Dim i As Integer
    With ActiveSheet
        i = 2
        Do
            'Mod operator
            If IsNumeric(.Range("B" & i)) And _
            IsNumeric(.Range("C" & i)) Then
                .Range("D" & i) = .Range("B" & i) Mod .Range("C" & i)
            End If
            i = i + 1
        Loop While .Range("A" & i) <> ""
    End With
    
End Sub

Microsoft Reference-Mod-operator
Microsoft Reference-Mod-operator example

Practice makes perfect. Thank You.

Comparison Operator Like in Excel VBA

 Comparison operator Like is for string versus pattern. The result after comparison is TRUE or FALSE, if TRUE then string matched with pattern and vice versa. Pattern consist character that represent certain condition as below. 

Character in Pattern		Matches in string
*		Nothing or Combination of any Characters.
#		Any Single digit number.
?		Any Single Character.
[]		Any single character inside 2 characters ex: [A-Z]
[!]		Any single character outside 2 Characters ex: [!A-Z]

Below example is from Microsoft Reference -Like Operator.

 Explanation:

No			Result			Explanation
1			True			"a" before and after Matched, "BBB" Matched with "*"
2			True			"F" Matched single Character within "[A-Z]"
3			False			"F" Not matched other than Character within "[!A-Z]"
4			True			"a" before and after Matched, "2" Matched with "#"
5			True			"a" Matched, "M" Matched [L-P], "5" Matched "#", "b" Matched "[!c-e]"
6			True			"B" Matched, "A" Matched "?", T Matched, "123khg" Matched with "*"
7			False			"B" Not matched "C", "A" Matched "?", T Matched, "123khg" Matched with "*"
8			True			"a" Matched, Nothing Matched with "*", "b" Matched
9			False			"a" Not matched with "a ", "*" Matched "[*]", "b" Matched
10			False			"a" Matched, "xxxxx" Not matched "[*]", "b" Matched
11			True			"a " Matched, "[" Matched "[[]", "xyz" Matched "*"
12			Error :93			Incomplete pattern (Error 93)

VBA Code:

Option Explicit
Sub ComparisonOperator_Like()

    Dim i As Integer
    Dim StrText As String
    Dim StrPtrn As String
    Dim Cell As Range, MyRng As Range
    With ActiveSheet
        i = 2
        Do
            'Assign Variables
            StrText = .Range("B" & i)
            StrPtrn = .Range("C" & i)

            On Error Resume Next
            'Compare string and pattern with operator like
            .Range("D" & i) = StrText Like StrPtrn
            
            'Indicate Error Code if failed
            If Err.Number <> 0 Then
                .Range("D" & i) = "Error :" & Err.Number
            End If
            
            'Reset Variables
            Err.Clear
            StrText = "": StrPtrn = ""
            i = i + 1
        Loop While .Range("A" & i) <> ""
        
        'To put background color base on result
        Set MyRng = .Range("D2:D13")
        For Each Cell In MyRng
            If Cell.Text = "TRUE" Then
                Cell.Interior.ColorIndex = 4 'Green
            ElseIf Cell.Text = "FALSE" Then
                Cell.Interior.ColorIndex = 6 'Yellow
            Else
                Cell.Interior.ColorIndex = 3 'Red
            End If
        Next
    End With
    
End Sub

Practice makes perfect. Thank You.

Comparison Operator Is in Excel VBA

Comparison operator Is basically consist of 3 type but type 2 is commonly use.

1) Direct compare 2 object with variables reference for example Ranges, Worksheets and etc.

Base on below table we try to compare 2 variables assign to different range.



VBA Code:

Option Explicit
Sub ComparisonOperator_Is()
    
    Dim i As Integer
    Dim RngA As Range
    Dim RngB As Range
    Dim RngC As Range
     
    With ActiveSheet
        Set RngA = .Range("A2:A4")
        Set RngB = .Range("B2:B4")
        Set RngC = RngA
        
        .Range("C2") = RngA Is RngB
        .Range("D2") = RngA Is RngC
        .Range("E2") = RngB Is RngC
        
        For i = 1 To 3
            If .Cells(2, 2 + i).Text = "TRUE" Then
                .Range(Cells(2, 2 + i), Cells(4, 2 + i)). _
                Interior.ColorIndex = 4
            Else
                .Range(Cells(2, 2 + i), Cells(4, 2 + i)). _
                Interior.ColorIndex = 6
            End If
        Next i
    End With
    
End Sub

2) Comparison with Is Nothing

Below example we try to find certain keyword (Ex. Score) inside any active sheet and get row and column index.

VBA Code:

Option Explicit
Sub ComparisonOperator_IsNothing()
    
    Dim StrKeyword As String
    Dim FndKeyword As Range
    Dim MyRng As Range
    
    StrKeyword = "Score"
    Set MyRng = ActiveSheet.UsedRange
    
    Set FndKeyword = MyRng.Find(StrKeyword, LookIn:=xlValues, _
    LookAt:=xlWhole)
    If FndKeyword Is Nothing Then
        MsgBox "Sorry the keyword" & StrKeyword & " was not found"
    Else
        MsgBox "The keyword found at Row index = " & FndKeyword.Row & _
        " Column index = " & FndKeyword.Column
    End If

End Sub

3) Comparison with Is Nothing with Intersect.

Below example to check either our selection is inside our designated range.

VBA Code:

Option Explicit
Sub ComparisonOperator_IsNothingInteSect()
    
    Dim MyRng As Range
    Dim MySelect As Range
    
    With ActiveSheet
        Set MyRng = .Range("A1:D10")
        Set MySelect = Selection
    
        If Not Intersect(MySelect, MyRng) Is Nothing Then
            MsgBox "Your Selected cell is in range A1 to D10."
        Else
            MsgBox "Your Selected cell is Not in range."
        End If
    End With
    
End Sub

Microsoft Reference-Is-operator

Practice makes perfect. Thank You.

Comparison Operator in Excel VBA

 Basically 6 types of comparison operator as below table.

No.			Operator			Compare Value
1			<			Less Than
2			<=			Less than or equal to
3			>			Greater than
4			>=			Greater than or equal to
5			=			Equal to
6			<>			Not equal to

Comparison operator to be use either compare 2 value or checking certain condition example empty or null but mostly to check numeric value as below either True or False. Inside Green and Yellow cell we have formula =Num1 Operator Num2 example inside Range("E3") =B3<D3 then answer is TRUE.

 

VBA Code:

Option Explicit
Sub ComparisonOperator()
    Dim i As Integer
    Dim Cell As Range, MyRng As Range
     
    With ActiveSheet
        i = 3
        Do
            'For < Less Than
            .Range("E" & i) = .Range("B" & i) < .Range("D" & i)
            'For <= Less than or equal to
            .Range("F" & i) = .Range("B" & i) <= .Range("D" & i)
            'For > Greater than
            .Range("G" & i) = .Range("B" & i) > .Range("D" & i)
            'For >= Greater than or equal to
            .Range("H" & i) = .Range("B" & i) >= .Range("D" & i)
            'For = Equal to
            .Range("I" & i) = .Range("B" & i) = .Range("D" & i)
            'For <> Not equal to
            .Range("J" & i) = .Range("B" & i) <> .Range("D" & i)
            i = i + 1
        Loop While .Range("A" & i) <> ""
        Set MyRng = .Range("E3:J5")
        For Each Cell In MyRng
            If Cell.Text = "TRUE" Then
                Cell.Interior.ColorIndex = 4
            Else
                Cell.Interior.ColorIndex = 6
            End If
        Next
    End With
    
End Sub

Microsoft Reference-Comparison-operators

Practice makes perfect. Thank You.

Basic Operator for Excel VBA

Before start your 1st code it is better to understand basic operator purpose and function as below table. Certain operator can be use as different function for example + and =.

No.

Category

Operator

Purpose

Example

Result

1

Numeric Variables

+

Sum

2 + 5

7

2

Numeric Variables

-

Minus

5 - 2

3

3

Numeric Variables

*

Multiply

5 * 5

25

4

Numeric Variables

/

Divide

5.5 / 2

2.75

5

Numeric Variables

^

Exponential

5.5 ^ 2

30.25

6

Numeric Variables

=

Equivalent

2 + 5 = 7

7

String/Variables

=

Assign

MyVar = "C:\MyFiles"

8

Property/Object

=

Assign

Set MyVar = ActiveSheet

9

String/Variables

+

Concatenation

"Hello " + "World"

Hello World

10

String/Variables

&

Concatenation

"Hello " & "World"

Hello World

11

Numeric Integer

\

Divide

5.5 / 2

2

The above table just a basic operator not comparison operator.

Microsoft Reference-Operator-summary
Microsoft Reference-Operator (* Operator)
Microsoft Reference-Caret-operator (^ operator)
Microsoft Reference-Plus-operator (+ operator)
Microsoft Reference-Ampersand-operator (& operator)
Microsoft Reference-Equals-operator (= operator)
Microsoft Reference-Minus-operator (- operator)
Microsoft Reference-Forwardslash-operator (/ operator)
Microsoft Reference-Backslash-operator (\ operator)

Practice makes perfect. Thank You.