vba 如果单元格的值等于另一列的任何值，则有条件地格式化单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27345379/
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
Conditionally formatting cells if their value equals any value of another column
提问by PeterInvincible
I have data in the Aand Bcolumns. Bcolumn's data is mostly duplicates of A's data, but not always. For example:
我在A和B列中有数据。Bcolumn 的数据主要是A's 数据的重复项，但并非总是如此。例如：
A
Budapest
Prague
Paris
Bukarest
Moscow
Rome
New York
B
Budapest
Prague
Los Angeles
Bukarest
I need to search the Acolumn for the values in B. If a row matches, I need to change the row's background colour in Ato red or something.
我需要在A列中搜索B. 如果一行匹配，我需要将行的背景颜色更改A为红色或其他颜色。
回答by Marcel
Here is the formula
这是公式
create a new rule in conditional formating based on a formula. Use the following formula and apply it to $A:$A
基于公式以条件格式创建新规则。使用以下公式并将其应用于$A:$A
=NOT(ISERROR(MATCH(A1,$B:$B00,0)))
here is the example sheet to download if you encounter problems
UPDATE
here is @pnuts's suggestion which works perfect as well:
更新
这里是@pnuts的建议，它也很完美：
=MATCH(A1,B:B,0)>0
回答by KERR
No formulas required. This works on as many columns as you need, but will only compare columns in the same worksheet:
不需要公式。这适用于您需要的任意数量的列，但只会比较同一工作表中的列：
NOTE: remove any duplicates from the individual columns first!
注意：首先从各个列中删除任何重复项！
- Select the columns to compare
- click Conditional Formatting
- click Highlight Cells Rules
- click Duplicate Values (the defaults should be OK)
Duplicates are now highlighted in red
- Bonus tip, you can filter each row by colour to either leave the unique values in the column, or leave just the duplicates.
- 选择要比较的列
- 单击条件格式
- 单击突出显示单元格规则
- 单击重复值（默认值应该是确定的）
重复项现在以红色突出显示
- 额外提示，您可以按颜色过滤每一行，以在列中保留唯一值，或仅保留重复值。
回答by async3
Another simpler solution is to use this formula in the conditional formatting (apply to column A):
另一个更简单的解决方案是在条件格式中使用此公式（适用于 A 列）：
=COUNTIF(B:B,A1)
Regards!
问候！
回答by peege
All you need to do for that is a simple loop.
This doesn't handle testing for lower case, upper-case mismatch.
If this isn't exactly what you are looking for, comment, and I can revise.
您需要做的只是一个简单的循环。
这不处理小写、大写不匹配的测试。如果这不是您正在寻找的内容，请发表评论，我可以修改。
If you are planning to learn VBA. This is a great start.
如果您打算学习VBA。这是一个很好的开始。
TESTED:
测试：
Sub MatchAndColor()
Dim lastRow As Long
Dim sheetName As String
sheetName = "Sheet1" 'Insert your sheet name here
lastRow = Sheets(sheetName).Range("A" & Rows.Count).End(xlUp).Row
For lRow = 2 To lastRow 'Loop through all rows
If Sheets(sheetName).Cells(lRow, "A") = Sheets(sheetName).Cells(lRow, "B") Then
Sheets(sheetName).Cells(lRow, "A").Interior.ColorIndex = 3 'Set Color to RED
End If
Next lRow
End Sub
回答by Damian Duran
I was looking into this and loved the approach from peege using a for loop! (because I'm learning VBA at the moment)
我正在研究这个并喜欢使用 for 循环的 peege 方法！（因为我现在正在学习 VBA）
However, if we are trying to match "any" value of another column, how about using nested loops like the following?
但是，如果我们试图匹配另一列的“任何”值，那么使用如下嵌套循环如何？
Sub MatchAndColor()
Dim lastRow As Long
Dim sheetName As String
sheetName = "Sheet1" 'Insert your sheet name here
lastRow = Sheets(sheetName).Range("A" & Rows.Count).End(xlUp).Row
For lRowA = 1 To lastRow 'Loop through all rows
For lRowB = 1 To lastRow
If Sheets(sheetName).Cells(lRowA, "A") = Sheets(sheetName).Cells(lRowB, "B") Then
Sheets(sheetName).Cells(lRowA, "A").Interior.ColorIndex = 3 'Set Color to RED
End If
Next lRowB
Next lRowA
End Sub
回答by Jessica Rapson
I unable to comment on the top answer, but Excel actually lets you do this without adding the ugly conditional logic.
我无法对最佳答案发表评论，但 Excel 实际上可以让您在不添加丑陋条件逻辑的情况下执行此操作。
Conditional formatting is automatically appliedto any input that isn't an error, so you can achieve the same effect as:
条件格式会自动应用于任何不是错误的输入，因此您可以获得与以下相同的效果：
=NOT(ISERROR(MATCH(A1,$B:$B00,0)))
With this:
有了这个：
= MATCH(A1,$B:$B00,0)))
If the above is applied to your data, A1 will be formatted if it matches any cell in $B$1:$B$1000, as any non-match will return an error.
如果将上述内容应用于您的数据，如果 A1 与 $B$1:$B$1000 中的任何单元格匹配，则将对其进行格式化，因为任何不匹配都会返回错误。
