fluffyvampirekitten
Board Regular
- Joined
- Jul 1, 2015
- Messages
- 72
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
However i only get errors at #N/A part .
Any ideas why?
Thanks in advance
Code:
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