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