EXCEL VBA-比较不同工作表中的列，突出显示不重叠的内容
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16304997/
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
EXCEL VBA- Compare columns in different worksheets, highlight non-overlapping content
提问by Parseltongue
So, I'm new to VBA, so go easy.
所以，我是 VBA 的新手，所以放轻松。
I have two different worksheets-- Merged Nativity Codes and 2007-2011 Nativity Codes. I want to iterate through all the values in the 2007-2011 codes (Column B) and compare that to the values in Column A of Merged Nativity Codes. I want to highlight the content that is in the 2007-2011 codes, but not in the Merged Nativity Codes.
我有两个不同的工作表 - 合并的诞生代码和 2007-2011 年诞生代码。我想遍历 2007-2011 年代码（B 列）中的所有值，并将其与合并的诞生代码 A 列中的值进行比较。我想突出显示 2007-2011 代码中的内容，而不是合并的诞生代码中的内容。
I tried my hand at this VBA macro:
我尝试了这个 VBA 宏：
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet
Set ws1 = Worksheets("2002-2006 Nativity Codes")
Set ws2 = Worksheets("Merged Nativity Codes")
Set ws3 = Worksheets("2007-2011 Nativity Codes")
For Each i In ws3.Range("B2:B154")
For Each C In ws2.Range("A1:A138")
If i.Cells.Value <> C.Cells.Value Then
i.Cells.Interior.ColorIndex = 3
End If
Next C
Next i
But the problem is that " If i.Cells.Value <> C.Cells.Value Then" will be triggered if only one doesn't match. I'm looking for something like the "all()" function in Python. Does this exist? Easier way to do this?
但问题是如果只有一个不匹配，就会触发“If i.Cells.Value <> C.Cells.Value Then”。我正在寻找类似于 Python 中的“all()”函数的东西。这存在吗？更简单的方法来做到这一点？
回答by Parseltongue
Answered my own question by taking the easy way out. I first highlighted the columns of interest with red highlighting. Then I changed the Macro to color in the columns CLEAR if it finds a match (rather than coloring it red if it doesn't find one).
通过采取简单的方法回答我自己的问题。我首先用红色突出显示了感兴趣的列。然后，如果找到匹配项，我将宏更改为 CLEAR 列中的颜色（如果找不到匹配项，则将其着色为红色）。
**Note- I'm new to this stuff, so if you guys have a more efficient way of answering this, I'd be happy to learn. I feel like vLookUp might be useful.
**注意 - 我是这个东西的新手，所以如果你们有更有效的方法来回答这个问题，我很乐意学习。我觉得 vLookUp 可能有用。
For Each i In ws1.Range("B2:B154")
For Each C In ws2.Range("A1:A138")
If i.Cells.Value = C.Cells.Value Then
i.Cells.Interior.ColorIndex = xlNone
End If
Next C
Next i
