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.