Highlighting Values of a Cell when the Cell contains #N/A

[wao5012]

Hello All,

I am using the below script to highlight values of cells in a specific column when they contain #N/A. I recieve an error called "Type Mismatch" on the highlighted line below (in Red).
Sub highlightValues()


Sub highlightValues()


Dim ws As Worksheet
Dim i As Long
Dim lastrow As Long
Set ws = Worksheets("sheet1")
lastrow = ws.Cells(Rows.Count, 1).End(xlUp).Row


For i = 3 To lastrow
If Cells(i, 18).Value = "#N/A" Then
Cells(i, 18).Interior.Color = RGB(256, 256, 256)
Else
Cells(1, 18).Interior.Color = xlNone
End If
Next


End Sub

 

[Fluff]

Rick to give you some background. I built out a VBA code that performs a vlookup to return values (in formula form), then i copy and past the values as values. I just want to find where cell equals #N/A highlight red. i shouldn't get any other error so i am sure i don't need the specific error.

Bearing in mind the part in red try

Columns("R").SpecialCells(xlConstants, xlErrors).Interior.Color = vbRed

 

[Rick Rothstein]

Bearing in mind the part in red try

Columns("R").SpecialCells(xlConstants, xlErrors).Interior.Color = vbRed

I completely missed the "then i copy and past the values as values" part of the OP's post.

By the way, you should add the On Error trap I show in Message #10 in case there are no errors.

 

[Scott Huish]

This could also be done with Conditional Formatting.

 

[Fluff]

By the way, you should add the On Error trap I show in Message#10 in case there are no errors.

Agreed

 

[wao5012]

The column is Column R. As stated earlier prob need to clarify i have a VBA code that performs and vlookup and then copay and paste column R to give me values. So there are no formulas and no errors in the cells. When you copy and paste values the "#N/A" is the pasted value of the error.

 

[wao5012]

Scratch my last comment. Thank you Rick and Fluff for your input as i run the code with those couple lines it works. Thanks for your help it is appreciated.