vba 如何在VBA中的单元格中填充颜色？

How to fill color in a cell in VBA?

提问by Ale

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

我想为当前表中具有“#N/A”值的单元格着色。为了做到这一点，我使用以下宏：

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" Thengives an error: Type mismatch. Maybe someone can help to understand where is the error? Thanks

但该行If cell.Value = "#N/A" Then给出了一个错误：类型不匹配。也许有人可以帮助理解错误在哪里？谢谢

采纳答案by Dmitry Pavliv

Non VBA Solution:

非 VBA 解决方案：

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

将条件格式规则与公式一起使用：（=ISNA(A1)突出显示所有错误的单元格- 不仅#N/A使用=ISERROR(A1)）

VBA Solution:

VBA解决方案：

Your code loops through 50 mlncells. 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)

您的代码循环遍历5000 万个单元格。为了减少单元格的数量，我使用.SpecialCells(xlCellTypeFormulas, 16)并.SpecialCells(xlCellTypeConstants, 16)仅返回有错误的单元格（注意，我正在使用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

注意，要突出显示有任何错误的单元格（不仅"#N/A"），请替换以下代码

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)

UPD：（如何通过VBA添加CF规则）

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

回答by teylyn

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" Thenwill 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.

1. 使用条件格式而不是 VBA 来突出显示错误。

2. 使用像您发布的那样的 VBA 循环将需要很长时间来处理

3. 该声明If cell.Value = "#N/A" Then永远不会奏效。如果您坚持使用 VBA 来突出显示错误，请尝试使用此方法。

   子 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. 准备好等待很长时间，因为该过程会循环遍历 5100 万个单元格

5. 有更有效的方法来实现您想做的事情。如果您改变主意，请更新您的问题。

回答by Alpha

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

1. 按左上角选择所有单元格
2. 选择【首页】>>【条件格式】>>【新规则】
3. 选择[仅格式化包含的单元格]
4. 在 [Format only cells with:] 中，选择“Errors”
5. 在 [Format..] 按钮中选择正确的格式

回答by Dustin Knight

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.

您需要使用 cell.Text = "#N/A" 而不是 cell.Value = "#N/A"。单元格中的错误实际上只是存储在单元格中的文本。