在 Excel 2010 中，使用宏和 VBA 比较列中的数据并突出显示值（如果不同）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21189790/
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
In Excel 2010 compare data from columns and highlight values if different using macro and VBA
提问by dan1974
I have two columns, from 2 worksheets. In the first worksheet a column contains a list of craftspeople assigned to a task. The second worksheet contains just a list of the craftspeople. I need to compare the craftspeople from the first worksheet and highlight a cell if its value does not match any value in the second worksheet.
我有两列，来自 2 个工作表。在第一个工作表中，一列包含分配给任务的工匠列表。第二个工作表仅包含工匠名单。我需要比较第一个工作表中的工匠，如果它的值与第二个工作表中的任何值不匹配，则突出显示一个单元格。
We could end up with a list of say 50 craftspeople in sheet2 and they could be assigned to multiple tasks in the first sheet (there could be hundreds of tasks), so the columns will not be the same length.
我们最终可以在 sheet2 中得到一个 50 名工匠的列表，并且他们可以被分配到第一张表中的多个任务（可能有数百个任务），因此列的长度不会相同。
When we run the Marco we want any cells with a value (one sheet 1) that does not match the values in the second sheet to highlight in red and replace the text with the statement "Incorrect Name"
当我们运行 Marco 时，我们希望任何具有与第二张工作表中的值不匹配的值（一张工作表 1）的单元格以红色突出显示，并用语句“名称不正确”替换文本
I have some code which I found via one of the searches on this site or another, and have modified it, so it's close to what I want. But it highlights the data the wrong way round, its highlighting the values which match, I want the ones that do not match to be highlighted! I've tried but have been unable to correct this - can anyone help, and also tidy up the code???
我有一些代码是通过在本网站或其他网站上的搜索之一找到的，并对其进行了修改，因此它与我想要的很接近。但它以错误的方式突出显示数据，突出显示匹配的值，我希望突出显示不匹配的值！我试过了，但一直无法纠正这个问题 - 任何人都可以帮忙，还可以整理一下代码吗？？？
Sub CompareAndHighlight()
Dim rng1 As Range, rng2 As Range, i As Integer, j As Integer
For i = 1 To Sheets("workorders").Range("U" & Rows.Count).End(xlUp).Row
Set rng1 = Sheets("workorders").Range("U" & i)
For j = 1 To Sheets("craftspersondata").Range("A" & Rows.Count).End(xlUp).Row
Set rng2 = Sheets("craftspersondata").Range("A" & j)
If StrComp(Trim(rng1.Text), Trim(rng2.Text), vbTextCompare) = 0 Then
rng1.Interior.Color = RGB(255, 0, 0)
rng1.Value = "Incorrect Name"
End If
Set rng2 = Nothing
Next j
Set rng1 = Nothing
Next i
End Sub
I've tried changing the following line:
我尝试更改以下行：
If StrComp(Trim(rng1.Text), Trim(rng2.Text), vbTextCompare) = 0 Then
如果 StrComp(Trim(rng1.Text), Trim(rng2.Text), vbTextCompare) = 0 那么
to: If StrComp(Trim(rng1.Text), Trim(rng2.Text), vbTextCompare) <> 0 Then
到：如果 StrComp(Trim(rng1.Text), Trim(rng2.Text), vbTextCompare) <> 0 然后
but this highlights every row in the column, so doesn't give ne the fix I need...
但这突出显示了列中的每一行，所以没有给出我需要的修复......
采纳答案by Dmitry Pavliv
Try to use following code:
尝试使用以下代码：
Sub CompareAndHighlight()
Dim rng1 As Range, rng2 As Range, i As Integer, j As Integer
Dim isMatch As Boolean
For i = 2 To Sheets("workorders").Range("U" & Rows.Count).End(xlUp).Row
isMatch = False
Set rng1 = Sheets("workorders").Range("U" & i)
For j = 1 To Sheets("craftspersondata").Range("A" & Rows.Count).End(xlUp).Row
Set rng2 = Sheets("craftspersondata").Range("A" & j)
If StrComp(Trim(rng1.Text), Trim(rng2.Text), vbTextCompare) = 0 Then
isMatch = True
Exit For
End If
Set rng2 = Nothing
Next j
If Not isMatch Then
rng1.Interior.Color = RGB(255, 0, 0)
rng1.Value = "Incorrect Name"
End If
Set rng1 = Nothing
Next i
End Sub
回答by Dave Sexton
Why are you trying to do this with a macro - it would be far easier to use conditional formatting?
为什么要尝试使用宏来执行此操作 - 使用条件格式会容易得多？
Select the column that you are checking against and then hit Ctrl + F3 to create a named range. Call it CRAFT_PEOPLE for example.
选择您要检查的列，然后按 Ctrl + F3 以创建命名范围。例如，称之为 CRAFT_PEOPLE。
Next select the column in which you want to display the highlighting. From the Home ribbon select Conditional Formatting > New Rule > Use a Formula (last one on the list).
接下来选择要在其中显示突出显示的列。从主页功能区中选择条件格式> 新规则> 使用公式（列表中的最后一个）。
For the formula enter:
对于公式输入：
=ISERROR(MATCH(A1,CRAFT_PEOPLE,0))
Replace A1 with the cell reference of the active cell of your current selection - make sure it has no $'s in the reference. Next hit the formatting button and pick how you want to highlight the matches.
将 A1 替换为当前选择的活动单元格的单元格引用 - 确保引用中没有 $ 。接下来点击格式化按钮并选择您想要突出显示匹配的方式。
Click OK
单击确定
