vba 突出显示 Sheet1 中与 Sheet2 中的值匹配的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12763755/
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
Highlight Rows in Sheet1 that match values in Sheet2
提问by Spencer
It's been a long time since I've done anything advanced in Excel. I have Excel 2010. I've watch many videos and tried some tutorials that do sort of what I'm looking for, but I'm just missing something.
我已经很久没有在 Excel 中做过任何高级的事情了。我有 Excel 2010。我看过很多视频并尝试了一些教程，这些教程可以做我正在寻找的东西，但我只是错过了一些东西。
This is what I'm trying to accomplish... I have a list of about 50 SKUs in Sheet2. I have a complete list of 200 Products in Sheet1.
这就是我想要完成的……我在 Sheet2 中有一个大约 50 个 SKU 的列表。我在 Sheet1 中有 200 个产品的完整列表。
SHEET1:
表 1：
ColA are SKUs
ColB is Desc
ColC is Price
SHEET2:
表 2：
ColA are SKUs
I need a formula or Macro that will look at all SKUs in Sheet2, then find any matches in Sheet1 ColA, then highlight the rows where there is a match.
我需要一个公式或宏来查看 Sheet2 中的所有 SKU，然后在 Sheet1 ColA 中找到任何匹配项，然后突出显示匹配项的行。
I would really appreciate any help you can provide, even if it's just a link to an exact example. Thank you!
我真的很感激你能提供的任何帮助，即使它只是一个确切例子的链接。谢谢！
回答by andy holaday
If you just want to mark matching rows you can do something easy. This will return matching SKUs, or #N/A if no match:
如果您只想标记匹配的行，您可以做一些简单的事情。这将返回匹配的 SKU，如果不匹配，则返回 #N/A：
=VLOOKUP(A2,Sheet2!$A:$A,1,FALSE)
=VLOOKUP(A2,Sheet2!$A:$A,1,FALSE)
If you really want highlighting you could use the helper formula above and set up conditional formatting (CF) over the range. The CF formula will be something like
如果您真的想要突出显示，您可以使用上面的辅助公式并在该范围内设置条件格式 (CF)。CF 公式将类似于
=NOT(ISNA($D2))(assuming you put the VLOOKUPs in column D)
=NOT(ISNA($D2))（假设您将VLOOKUPs 放在 D 列中）
There is another way to do CF that uses no helper formula. First you need to set up a named range on Sheet2 A:A. I'll call it SKUsin this example.
还有另一种不使用辅助公式的 CF 方法。首先，您需要在 上设置命名范围Sheet2 A:A。我会SKUs在这个例子中调用它。
Then a CF formula like this will tag matching rows:
然后像这样的 CF 公式将标记匹配的行：
=MATCH($A2,SKUs,0)>0
=MATCH($A2,SKUs,0)>0
Edit: I am assuming the data (and CF range if you use that) starts in row 2, allowing for a header in row 1.
编辑：我假设数据（和 CF 范围，如果你使用它）从第 2 行开始，允许在第 1 行有一个标题。
回答by cr1msonmyst
This may be a little late, but I figured I would still add my 2 cents. I use the following formula to do something similar... =IFERROR(IF(VLOOKUP(B1,Sheet2!$A:$A,1,FALSE)>0,"Y","N"),"N")
这可能有点晚了，但我想我仍然会添加我的 2 美分。我使用以下公式来做类似的事情... =IFERROR(IF(VLOOKUP(B1,Sheet2!$A:$A,1,FALSE)>0,"Y","N"),"N")
Basically I just have a column of Y or N for if that item is also on "Sheet2" and this is the formula that decides whether it is a Y or N.
基本上，如果该项目也在“Sheet2”上，我只有一列 Y 或 N，这是决定它是 Y 还是 N 的公式。
回答by ApplePie
Just use your VLOOKUP() along with IFERROR() in a conditional format formula.
只需在条件格式公式中使用 VLOOKUP() 和 IFERROR() 即可。
Select the range you would like to apply conditional formatting then do Home -> Conditional Formatting (in 2007) then "Apply a formula".
选择您想要应用条件格式的范围，然后执行 Home -> Conditional Formatting (in 2007) 然后“Apply a formula”。
Then you'll want to apply a formula more or less like this one:
然后你会想应用一个或多或少像这样的公式：
=IF(IFERROR(VLOOKUP($A2, Sheet2!$D$2:$D$4, 1, 0)), 0, 1)
=IF(IFERROR(VLOOKUP($A2, Sheet2!$D$2:$D$4, 1, 0)), 0, 1)
Just adapt the ranges to your needs. I tested this to work on 2007.
只需根据您的需要调整范围。我测试了这个在 2007 年工作。
回答by Siraj Samsudeen
You can use conditional formatting in Excel 2010 to achieve what you want to do.
您可以在 Excel 2010 中使用条件格式来实现您想要执行的操作。
There are 2 ways to do this. The first one works only with the ranges in one sheet whereas the second one lets you work across sheets.
有两种方法可以做到这一点。第一个仅适用于一张工作表中的范围，而第二个允许您跨工作表工作。
Assuming you can copy and paste both the ranges in one worksheet, you can select both ranges using Control key. After selecting the ranges, Go to Home->Conditional Formatting->Highlight Cell Rules->Duplicate Values. Now Select Duplicate in the dialog box and it should highlight the names in Range 1 that are appearing in Range 2 (your original SKU list).
If you can't copy and paste the second range into the same worksheet, then you have to use a formula with conditional formatting. Select the used range in ColA in sheet1, Go to Home->Conditional Formatting->New Rule. Now Select the Rule Type 'Use a formula to determine which cells to format'. Now type the formula like this (this formula assumes that your cursor is in A1 when you apply invoke the dialog)
=COUNTIF(Sheet2!$A$1:$A$3,Sheet1!A1)
假设您可以将两个范围复制并粘贴到一张工作表中，您可以使用 Control 键选择两个范围。选择范围后，转到主页->条件格式->突出显示单元格规则->重复值。现在在对话框中选择重复，它应该突出显示范围 1 中出现在范围 2（您的原始 SKU 列表）中的名称。
如果您无法将第二个区域复制并粘贴到同一个工作表中，则必须使用带有条件格式的公式。在sheet1中选择ColA中使用的范围，转到首页->条件格式->新建规则。现在选择规则类型“使用公式来确定要设置格式的单元格”。现在键入这样的公式（此公式假定您在应用调用对话框时光标位于 A1 中）
=COUNTIF(Sheet2!$A$1:$A$3,Sheet1!A1)
That should highlight in Sheet1 all the items found in Sheet2. Edit the above formula to include the correct range for your situation and use a dynamic range if you know how to do it.
这应该在 Sheet1 中突出显示在 Sheet2 中找到的所有项目。编辑上述公式以包含适合您情况的正确范围，并在您知道如何操作时使用动态范围。
I have used approach 1 countless times and I have just tested approach 2 with a sample and it works. If they don't work for you, please let me know and I can help you further.
我已经无数次使用方法 1，我刚刚用一个样本测试了方法 2，它有效。如果它们不适合您，请告诉我，我可以进一步帮助您。
