如何在VBA中填充单元格中的颜色? [英] How to fill color in a cell in VBA?
问题描述
我想在电流表中显示具有#N / A值的单元格。为了做到这一点,我使用以下宏:
Sub ColorCells()
Dim Data As Range
Dim cell As Range
设置电流表= ActiveWorkbook.Sheets(比较)
设置数据=currentheet.Range(A2:AW1048576)
对于每个单元格在数据
如果cell.Value =#N / A然后
cell.Interior.ColorIndex = 3
结束如果
下一个
结束Sub
但行如果cell.Value =#N / A然后
给出错误:类型不匹配。也许有人可以帮助了解错误在哪里?谢谢
非VBA考试:
使用CF规则,其公式如下: = ISNA(A1)
(使用所有错误来高清单元 - 不仅 N / A
,使用 = ISERROR(A1)
)
VBA解决方案:
您的代码循环通过<> 50 ml 单元格。为了减少单元格的数量,我使用 .SpecialCells(xlCellTypeFormulas,16)
和 .SpecialCells(xlCellTypeConstants,16)
只返回单元格错误(注意,我使用如果cell.Text =#N / A然后
)
数据2作为范围,数据2作为范围,单元格作为范围
Dim电流表作为工作表
设置电流表= ActiveWorkbook.Sheets(比较)
与电流表.Range(A2:AW& Rows.Count)
.Interior.Color = xlNone
错误恢复Next
'仅选择带有错误的单元格
设置Data = .SpecialCells(xlCellTypeFormulas,16)
设置Data2 = .SpecialCells(xlCellTypeConstants,16)
错误GoTo 0
结束
如果不是数据2不是然后
如果不是数据没有然后
设置数据=联合(数据,数据2)
Else
设置Data = Data2
结束I f
如果
如果不是数据,则
对于每个单元格在数据
如果cell.Text =#N / A然后
cell.Interior.ColorIndex = 4
End If
Next
End If
End Sub
注意,要突出显示任何错误的单元格(不仅#N / A
),请替换以下内容代码
如果不是数据没有,然后
对于每个单元格在数据
如果cell.Text = #N / A然后
cell.Interior.ColorIndex = 3
结束如果
下一个
结束如果
与
如果不是数据则不是Data.Interior.ColorIndex = 3
UPD:(如何通过VBA添加CF规则) p>
Sub test()
With ActiveWorkbook.Sheets(Comparison)。Range(A2:AW& Rows.Count).FormatConditions
.Delete
.Add类型:= xlExpression,Formula1:== ISNA(A1)
.Item(1).Interior.ColorIndex = 3
结束
结束子
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
Non VBA colution:
use CF rule with formula: =ISNA(A1)
(to higlight cells with all errors - not only #N/A
, use =ISERROR(A1)
)
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 error (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
这篇关于如何在VBA中填充单元格中的颜色?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!