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

 

[Rick Rothstein]

If Cells(i, 18).Value = "#N/A" Then

The #N/A you see in the cell is not text, so you cannot test against the quoted text your code line is testing for, rather, #N/A is a Cell Value Error so you must use the CVErr function to test for the specific error value. Change the above line of code to this and it should then work...

If Cells(i, 18).Value = CVErr(xlErrNA) Then

If you look up "Cell Error Values" (without the quotes) in the VB Editor's search box, you should get a link to a help page that shows you all the possible error Cell Value Errors that you can test for.

 

[wao5012]

Hey Rick, I am still getting an error "type mismatch" on the line below

If Cells(i, 18).Value = CVErr(xlErrNA) Then

 

[Special-K99]

Isn't this line wrong?

Cells(1, 18).Interior.Color = xlNone

Shouldn't this be

Cells(i, 18).Interior.Color = xlNone

That is, loop through from row 3 to the last row and either highlight the cell with a color or no color.
The way you have it at the moment it's highlighting cell R1 which is outside the loop.

 

[wao5012]

I made the changes but i am still seeing the same error

 

[Rick Rothstein]

Hey Rick, I am still getting an error "type mismatch" on the line below

If Cells(i, 18).Value = CVErr(xlErrNA) Then

Yeah, I am thinking you have to make sure the cell has an error in it before you can test for a specific error number. Something like this maybe...

If IsError(Cells(i, 18).Value Then
  If Cells(i, 18).Value = CVErr(xlErrNA) Then
    MsgBox "A #N/A error occurred"
  Else
    MsgBox "An error occurred, but it was not a #N/A error"
  End If
Else
  MsgBox "The cell does not contain an error"
End If

Do you actually care what the error specifically is? If not, there is simpler code available.

As a side note... in the original code you posted, you set the ws variable to a specific sheet name and you referenced it when determining the last row number, but you did not reference it for the Cells object in your loop. You should add the reference there otherwise the Cells object will look at the active worksheet for its values.

 

[wao5012]

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.

I also get an error when i try "Invaild Qualifier" on the IsError

 

[Rick Rothstein]

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.

These two lines of code should be all you need then. They will first remove all colors from Column R and then highlight those formulas in Column R that are displaying an error in red (no other code should be needed)...

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

 

[wao5012]

Below is the code i am using however it returns a error "No cells where Found"

Sub highlightValues()


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


End Sub

 

[Rick Rothstein]

Below is the code i am using however it returns a error "No cells where Found"

Sub highlightValues()


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


End Sub

The cells you want to examine are in Column R, correct? (Your original code specified column number 18 which is Column R.)

Your cells have formulas in them correct? (You would not be getting #N/A errors otherwise.)

Is at least one cell displaying an error? If not, then that is why you are getting the error. I was negligent and should have added two additional lines of code to protect against the possibility that no cells have errors in them...

Columns("R").Interior.Color = xlNone
On Error Resume Next
Columns("R").SpecialCells(xlFormulas, xlErrors).Interior.Color = vbRed
On Error GoTo 0