vba 具有特定数据的颜色单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18621509/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Color cells with specific data
提问by SONIA
I have a macro to color cells that have the word VOID in it.
我有一个宏来为其中包含 VOID 一词的单元格着色。
I also have the word VOID in a cell like this: [$189.00VOID].
我在这样的单元格中也有单词 VOID：[$189.00VOID]。
I can't find a way to color all cells that contain:
我找不到为包含以下内容的所有单元格着色的方法：
VOID and [$189.00VOID]
VOID 和 [$189.00VOID]
or any other dollar amount in it.
或其中的任何其他美元金额。
Sub Macro1()
On Error Resume Next
Dim current As String
For i = 1 To 65536 ' go from first cell to last
current = "c" & i ' cell counter
Range(current).Select ' visit the current cell
If Range(current).Text = "VOID" Then ' if it says VOID then we...
With Selection.Interior
.ColorIndex = 3 ' ...go red
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
End If
If Range(current).Text = "FORWARDED" Then ' if it says FORWARDED then we...
With Selection.Interior
.ColorIndex = 4 ' ...go green
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
End If
Next i ' loop and check the next cell
End Sub
回答by Arconath
VBA really seems like overkill for this. As pnuts said, conditional formatting will do everything you need.
VBA 真的看起来有点矫枉过正。正如 pnuts 所说，条件格式可以满足您的一切需求。
Select the cell(s) you want to format, then Home Ribbon -> Conditional Formatting -> New Rule -> Format Only Cells that Contain
选择要格式化的单元格，然后主页功能区 -> 条件格式 -> 新规则 -> 仅格式化包含的单元格
Then change the first combo box from Cell Value to specific text. and in the empty text box on the right type VOID.
然后将第一个组合框从 Cell Value 更改为特定文本。并在右侧的空白文本框中键入 VOID。
You can then adjust the cell formatting to be whatever you want.
然后，您可以将单元格格式调整为您想要的任何格式。
回答by tigeravatar
For something like this, I would really recommend using conditional formatting (as has already been stated). Here are the two Condtional Format formulas that you would need to apply to column C:
对于这样的事情，我真的建议使用条件格式（如前所述）。以下是您需要应用于 C 列的两个条件格式公式：
=COUNTIF($C1,"*VOID*")>0
=COUNTIF($C1,"*FORWARDED*")>0
However, if it absolutely has to be VBA, then right-click the sheet tab that you want to monitor and select "View Code". In there, paste the following:
但是，如果它绝对必须是 VBA，则右键单击要监视的工作表选项卡并选择“查看代码”。在那里，粘贴以下内容：
Private Sub Worksheet_Calculate()
Dim rngColor As Range
Dim rngFound As Range
Dim strFirst As String
Dim varFind As Variant
'Remove current formatting (if any)
Columns("C").Interior.Color = xlNone
'Check for both VOID and FORWARDED
For Each varFind In Array("VOID", "FORWARDED")
'Attempt to find a cell that contains varFind
Set rngFound = Columns("C").Find(varFind, Me.Cells(Me.Rows.Count, "C"), xlValues, xlPart)
'Check if any cells were found
If Not rngFound Is Nothing Then
'The first cell was found, record its address and start rngColor
strFirst = rngFound.Address
Set rngColor = rngFound
'Begin loop
Do
'Add found cell to rngColor
Set rngColor = Union(rngColor, rngFound)
'Advance loop by finding the next cell
Set rngFound = Columns("C").Find(varFind, rngFound, xlValues, xlPart)
'Exit loop when back to first cell
Loop While rngFound.Address <> strFirst
'Fill rngColor based on varFind
Select Case varFind
Case "VOID": rngColor.Interior.Color = vbRed
Case "FORWARDED": rngColor.Interior.Color = vbGreen
End Select
End If
Next varFind
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Worksheet_Calculate
End Sub
