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