[vba] How to fill color in a cell in VBA?

I would like to color cells that have "#N/A" value in the currentsheet. In order to do this i use following macro:

Sub ColorCells()

Dim Data As Range
Dim cell As Range
Set currentsheet = ActiveWorkbook.Sheets("Comparison")
Set Data = currentsheet.Range("A2:AW1048576")

For Each cell In Data
If cell.Value = "#N/A" Then
   cell.Interior.ColorIndex = 3
End If
Next

End Sub

But the line If cell.Value = "#N/A" Then gives an error: Type mismatch. Maybe someone can help to understand where is the error? Thanks

This question is related to vba excel

The answer is


Non VBA Solution:

Use Conditional Formatting rule with formula: =ISNA(A1) (to highlight cells with all errors - not only #N/A, use =ISERROR(A1))

enter image description here

VBA Solution:

Your code loops through 50 mln cells. To reduce number of cells, I use .SpecialCells(xlCellTypeFormulas, 16) and .SpecialCells(xlCellTypeConstants, 16)to return only cells with errors (note, I'm using If cell.Text = "#N/A" Then)

Sub ColorCells()
    Dim Data As Range, Data2 As Range, cell As Range
    Dim currentsheet As Worksheet

    Set currentsheet = ActiveWorkbook.Sheets("Comparison")

    With currentsheet.Range("A2:AW" & Rows.Count)
        .Interior.Color = xlNone
        On Error Resume Next
        'select only cells with errors
        Set Data = .SpecialCells(xlCellTypeFormulas, 16)
        Set Data2 = .SpecialCells(xlCellTypeConstants, 16)
        On Error GoTo 0
    End With

    If Not Data2 Is Nothing Then
        If Not Data Is Nothing Then
            Set Data = Union(Data, Data2)
        Else
            Set Data = Data2
        End If
    End If

    If Not Data Is Nothing Then
        For Each cell In Data
            If cell.Text = "#N/A" Then
               cell.Interior.ColorIndex = 4
            End If
        Next
    End If
End Sub

Note, to highlight cells witn any error (not only "#N/A"), replace following code

If Not Data Is Nothing Then
   For Each cell In Data
       If cell.Text = "#N/A" Then
          cell.Interior.ColorIndex = 3
       End If
   Next
End If

with

If Not Data Is Nothing Then Data.Interior.ColorIndex = 3

UPD: (how to add CF rule through VBA)

Sub test()
    With ActiveWorkbook.Sheets("Comparison").Range("A2:AW" & Rows.Count).FormatConditions
        .Delete
        .Add Type:=xlExpression, Formula1:="=ISNA(A1)"
        .Item(1).Interior.ColorIndex = 3
    End With
End Sub

  1. Select all cells by left-top corner
  2. Choose [Home] >> [Conditional Formatting] >> [New Rule]
  3. Choose [Format only cells that contain]
  4. In [Format only cells with:], choose "Errors"
  5. Choose proper formats in [Format..] button

You need to use cell.Text = "#N/A" instead of cell.Value = "#N/A". The error in the cell is actually just text stored in the cell.


  1. Use conditional formatting instead of VBA to highlight errors.

  2. Using a VBA loop like the one you posted will take a long time to process

  3. the statement If cell.Value = "#N/A" Then will never work. If you insist on using VBA to highlight errors, try this instead.

    Sub ColorCells()

    Dim Data As Range
    Dim cell As Range
    Set currentsheet = ActiveWorkbook.Sheets("Comparison")
    Set Data = currentsheet.Range("A2:AW1048576")
    
    For Each cell In Data
    
    If IsError(cell.Value) Then
       cell.Interior.ColorIndex = 3
    End If
    Next
    
    End Sub
    
  4. Be prepared for a long wait, since the procedure loops through 51 million cells

  5. There are more efficient ways to achieve what you want to do. Update your question if you have a change of mind.