excel - Hiding Reference Errors using Font colour in VBA

Question

Hi there i have this code which changes cells with reference errors to white fonts. However i could only do so for a single sheet. range. How do i change the for each loop to loop for all the worksheets in the workbook? I used this code below but it does not seem to work!

Sub Delete_ref_basedontextcondition()


    Dim R As Range


    'Set rng = Nothing
    On Error Resume Next
    Set R = Application.InputBox("Select cells To be deleted", Type:=8)
    Dim rng As Range



   If TypeName(R) <> "Range" Then
        Exit Sub
    Else
    R.Delete
   End If


For k = 1 To ThisWorkbook.Worksheets.Count 'runs through all worksheets

  Set wks = ThisWorkbook.Worksheets(k)


For Each cell In wks
 If cell.Text = "#REF!" Then
  cell.Font.Color = RGB(255, 255, 255)
 End If
Next
Next
End Sub

Answer 1

While I disagree with your method of hiding #REF! errors rather than dealing with them so that they are not #REF! errors (or deleting the formulas that are creating them, here is some standard 'loop-through-the-worksheets' code that you should be able to adapt for your purposes.

Sub bad_ref()
    Dim w As Long, ref As Range

    On Error Resume Next

    For w = 1 To Worksheets.Count
        With Worksheets(w)
            For Each ref In .Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
                If ref.Text = "#REF!" Then
                    ref.Font.ColorIndex = 2
                    'ref.clear   '<~~this clears formatting, formulas. etc from the rogue cell.
                End If
            Next ref
        End With
    Next w
End Sub

It should run through quickly enough. Rather than examine every cell on each worksheet, I've narrowed down the cells to be critiqued with the Range.SpecialCells method, looking only through the formulas that produce errors. Something like a #N/A error will be left alone.

I've left an option to actually do something with the errors as a commented line.