Selecting a range based on values of two other columns

Issue

I have two columns with the range O6:P that evaluate the row of data in J and display Pass or Fail. The results in O and in P are not always the same. One can display Passing and the other will have failed.
The code I have here works for the most part. Except that it is selecting the range in column J where either O or P are equal to PASS. I need it to only select the range where both columns are Passing. is there a way to split this up so that it will only select the range in J where both values in the row for O and P are passing?

Dim lastrow As Long
  Dim xRg As Range, yRg As Range, nRg As Range, mRg As Range

    'Selecting range = to PASS
    With ShNC1
        lastrow = .Cells(.Rows.Count, "J").End(xlUp).Row
        Application.ScreenUpdating = False
        For Each xRg In .Range("O6:P" & lastrow)
            If UCase(xRg.Text) = "PASS" Then
                If yRg Is Nothing Then
                    Set yRg = .Range("J" & xRg.Row)
                Else
                    Set yRg = Union(yRg, .Range("J" & xRg.Row))
                End If
            End If
        Next xRg

    End With

    If Not yRg Is Nothing Then yRg.Select

Solution

You could loop through just O and use Offset to check P:

Dim lastrow As Long
  Dim xRg As Range, yRg As Range, nRg As Range, mRg As Range

    'Selecting range = to PASS
    With ShNC1
        lastrow = .Cells(.Rows.Count, "J").End(xlUp).Row
        Application.ScreenUpdating = False
        For Each xRg In .Range("O6:O" & lastrow)
            If UCase(xRg.Text) = "PASS" And UCase(xRg.Offset(, 1).Text) = "PASS" Then
                If yRg Is Nothing Then
                    Set yRg = .Range("J" & xRg.Row)
                Else
                    Set yRg = Union(yRg, .Range("J" & xRg.Row))
                End If
            End If
        Next xRg

    End With

    If Not yRg Is Nothing Then yRg.Select

Answered By – SJR

Answer Checked By – Robin (AngularFixing Admin)

Leave a Reply

Your email address will not be published.