How to fix #N/A error

[fluffyvampirekitten]

I'm trying to highlight the cell which display "#N/A"
However i only get errors at #N/A part .
Any ideas why?
Thanks in advance

Private Sub Simpat4_Missing_UserName_Dept()
' Update User Name and Dept from Missing User name & dept xlsx file
Dim MaxRowNum, RowNum As Long
'Step 29
    With Application
        .ScreenUpdating = False
        .Calculation = xlManual
        
    End With
    
    Sheets("Simpat").Select
    ' Find the max number of row
    MaxRowNum = 1
       
    Do While Cells(MaxRowNum, 2) <> "" Or Cells(MaxRowNum + 1, 2) <> ""
        MaxRowNum = MaxRowNum + 1
    Loop
    
    'Find the row with the USERNAME & DEPT - "NOT INDICATED" and use a vlookup Function
    RowNum = 1
    
    For RowNum = 1 To MaxRowNum
    
        If UCase(Cells(RowNum, 16)) Like "*NOT INDICATED*" Or UCase(Cells(RowNum, 17)) Like "*NOT INDICATED*" Then
        
            'Vlookup User Name from the file MISSINGUSERNAMEDEPT.xlsx
            Range("P" & RowNum).FormulaR1C1 = "= VLOOKUP(RC[-3],[MISSINGUSERNAMEDEPT.xlsx]Sheet1!R1:R1048576,2,0)"
                       
            
            'Vlookup Dept from the file MISSINGUSERNAMEDEPT.xlsx
            Range("Q" & RowNum).FormulaR1C1 = "= VLOOKUP(RC[-4],[MISSINGUSERNAMEDEPT.xlsx]Sheet1!R1:R1048576,3,0)"
      
            
            ' Fill only the "NOT INDICATED" Cells
            ' Don't use Autofill as it will overwrite every row
            Range(Cells(RowNum, "P"), Cells(RowNum, "Q")).Select
            Selection.Copy
            Selection.PasteSpecial Paste:=xlPasteValues
        End If
        
        
        [COLOR=#FF0000]If UCase(Cells(RowNum, 16).Value) Like "*#N/A*" Then
            Cells(RowNum, 16).Interior.Color = RGB(0, 0, 255)
        End If
        
        If UCase(Cells(RowNum, 17).Value) Like "*#N/A*" Then
            Cells(RowNum, 17).Interior.Color = RGB(0, 0, 255)
        End If[/COLOR]
        
        
        
    Next
    
               
    With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
        
    End With
End Sub

 

[mole999]

you could just CF the sheet / columns to test for and display those errors

 

[StephenCrump]

.. or if you're set on using VBA, test:

WorksheetFunction.IsNA (Cells(RowNum, 16).Value)
'OR
Cells(RowNum, 16).Value = CVErr(xlErrNA)