Problem with formatting VLOOKUP results across multiple tabs

[spidaman]

Can anyone offer a fix please for a VLOOKUP problem I am struggling to resolve?

I am using a look-up table in "Book 2" to add the text value "YES" to columns on three different Sheets in "Book 1". I am then replacing the "#N/A" with "". Then using:

.SpecialCells(xlCellTypeConstants, 23).Select

...to select the cells left in the range with "YES" values. I then want to apply formatting to the "YES" cells in each of the sheets. I am currently getting an error with the second instance of

.SpecialCells(xlCellTypeConstants, 23).Select

...where I am trying to select the "YES" value cells on Sheet 2 of wbPI Workbook.

Why does the

.SpecialCells(xlCellTypeConstants, 23)

work for Sheet 1 but not for Sheet 2?
Can anyone identify the problem please or suggest a better way of removing the "#N/A" and selecting the remaining "YES" value cells?

This is the full code:

Sub LookUp_View()


Dim PAccept1 As Long, PInterv1 As Long, PInterv2 As Long, PInterv3 As Long
Dim wbPA As Workbook, wbPI As Workbook
Dim wsPA1 As Worksheet, wsPI1 As Worksheet, wsPI2 As Worksheet, wsPI3 As Worksheet
Dim PARang As Range


    Set wbPA = Workbooks("Book 2")
    Set wsPA1 = wbPA.Sheets(1)
    Set wbPI = Workbooks("Book 1")
    Set wsPI1 = wbPI.Sheets(1)
    Set wsPI2 = wbPI.Sheets(2)
    Set wsPI3 = wbPI.Sheets(3)
    
    PInterv1 = wsPI1.Range("A" & Rows.Count).End(xlUp).Row
    PInterv2 = wsPI2.Range("A" & Rows.Count).End(xlUp).Row
    PInterv3 = wsPI3.Range("A" & Rows.Count).End(xlUp).Row
    PAccept1 = wsPA1.Range("D" & Rows.Count).End(xlUp).Row
    
    Set PARang = wsPA1.Columns("G:H")


    
            wbPA.Activate
            
            wsPA1.Range("H:H").Insert
             
            Range("H1:H" & PAccept1).Value = "YES"
            
            Range("G:H").NumberFormat = "General"
            
                Cells.Select
                Range(Cells.Address).Sort Key1:=Range("G1"), Order1:=xlAscending, Header:= _
                xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
                DataOption1:=xlSortNormal




                wbPI.Activate
            
                wsPI1.Range("B:C").NumberFormat = "General"
                wsPI2.Range("B:C").NumberFormat = "General"
                wsPI3.Range("B:C").NumberFormat = "General"
                
                
                wsPI1.Range("C2:C" & PInterv1) = Application.VLookup(wsPI1.Range("B2:B" & PInterv1), PARang, 2, False)
                
                        With wsPI1.Range("C2:C" & PInterv1)
                            .Replace What:="#N/A", Replacement:="", LookAt:=xlPart, _
                             SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                             ReplaceFormat:=False
                            .Borders.LineStyle = xlContinuous
                        End With
                        
                        wsPI1.Range("C2:C" & PInterv1).SpecialCells(xlCellTypeConstants, 23).Select
                        
                        With Selection
                            .Interior.ColorIndex = 3
                            .Font.Bold = True
                            .Font.ColorIndex = 1
                            .HorizontalAlignment = xlCenter
                            .VerticalAlignment = xlCenter
                        End With
                    
    
                wsPI2.Range("C2:C" & PInterv2) = Application.VLookup(wsPI2.Range("B2:B" & PInterv2), PARang, 2, False)
                    
                        With wsPI2.Range("C2:C" & PInterv2)
                            .Replace What:="#N/A", Replacement:="", LookAt:=xlPart, _
                            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                            ReplaceFormat:=False
                            .Borders.LineStyle = xlContinuous
                        End With
    
' problem here!!
' debugger highlights next row:
' wsPI2.Range("C2:C" & PInterv2).SpecialCells(xlCellTypeConstants, 23).Select


                        wsPI2.Range("C2:C" & PInterv2).SpecialCells(xlCellTypeConstants, 23).Select


                        With Selection
                            .Interior.ColorIndex = 45
                            .Font.Bold = True
                            .Font.ColorIndex = 1
                            .HorizontalAlignment = xlCenter
                            .VerticalAlignment = xlCenter
                        End With




                wsPI3.Range("C2:C" & PInterv3) = Application.VLookup(wsPI3.Range("B2:B" & PInterv3), PARang, 2, False)
                
                        With wsPI3.Range("C2:C" & PInterv3)
                            .Replace What:="#N/A", Replacement:="", LookAt:=xlPart, _
                            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                            ReplaceFormat:=False
                            .Borders.LineStyle = xlContinuous
                        End With
    
                        wsPI3.Range("C2:C" & PInterv3).SpecialCells(xlCellTypeConstants, 23).Select


                        With Selection
                            .Interior.ColorIndex = 10
                            .Font.Bold = True
                            .Font.ColorIndex = 1
                            .HorizontalAlignment = xlCenter
                            .VerticalAlignment = xlCenter
                        End With
                        


End Sub

Thanks very much in advance