本文介绍了如何在VBA中填充单元格中的颜色?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧! 问题描述 29岁程序员,3月因学历无情被辞! 我想在电流表中显示具有#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 RangeDim cell As RangeSet currentsheet = ActiveWorkbook.Sheets("Comparison")Set Data = currentsheet.Range("A2:AW1048576")For Each cell In DataIf cell.Value = "#N/A" Then cell.Interior.ColorIndex = 3End IfNextEnd SubBut 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 IfEnd SubNote, to highlight cells witn any error (not only "#N/A"), replace following codeIf Not Data Is Nothing Then For Each cell In Data If cell.Text = "#N/A" Then cell.Interior.ColorIndex = 3 End If NextEnd Ifwith If Not Data Is Nothing Then Data.Interior.ColorIndex = 3UPD: (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 WithEnd Sub 这篇关于如何在VBA中填充单元格中的颜色?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持! 上岸,阿里云!
08-31 09:06