vba 突出显示其他工作表中不存在的行的宏
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15343811/
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
macro that highlights rows that do not exist in an other worksheet
提问by jardane
I have one file with two worksheets, both are full of names and addresses. I need a macro that will highlight rows in the first sheet if the cell A of that row does not match any rows from column A of the second sheet.
我有一个包含两个工作表的文件，两个工作表都充满了姓名和地址。如果该行的单元格 A 与第二个工作表的 A 列中的任何行都不匹配，我需要一个宏来突出显示第一张工作表中的行。
So if the first cell in a row has no matching data in any of the data in column A of sheet2 then that row is highlighted red.
因此，如果一行中的第一个单元格在 sheet2 的 A 列中的任何数据中都没有匹配的数据，则该行将突出显示为红色。
Also I might want to expand this in the future so could I also specify that Sheet1 can be the active sheet, but sheet2 is called by the sheet name?
另外我可能想在将来扩展它，所以我还可以指定 Sheet1 可以是活动工作表，但 sheet2 由工作表名称调用吗？
回答by
Try below code :
试试下面的代码：
Sub Sample()
Dim lastRow As Integer
Dim rng As Range
lastRow = Sheets("Sheet1").Range("A65000").End(xlUp).Row
For i = 1 To lastRow
Set rng = Sheets("sheet2").Range("A:A").Find(Sheets("Sheet1").Cells(i, 1))
If rng Is Nothing Then
Sheets("Sheet1").Cells(i, 1).EntireRow.Interior.Color = vbRed
End If
Next
End Sub
回答by Our Man in Bananas
here is an approach using a Worksheet formula:
这是使用工作表公式的方法：
=IF(ISERROR(VLOOKUP(A:A,Sheet2!A:A,1,FALSE)),"NOT FOUND",VLOOKUP(A:A,Sheet2!A:A,1,FALSE))
then you would use Conditional formatting to turn the cells red if column A doesn't find a match!
那么如果 A 列找不到匹配项，您将使用条件格式将单元格变为红色！
HTH
HTH
Philip
菲利普
回答by Our Man in Bananas
how about this:
这个怎么样：
Sub CondFormatting()
Range("D1:D" & Range("A1").End(xlDown).Row).Formula = "=IF(ISERROR(VLOOKUP(A:A,Sheet2!A:A,1,FALSE)),""NOT FOUND"",VLOOKUP(A:A,Sheet2!A:A,1,FALSE))"
With Columns("D:D")
.FormatConditions.Delete
.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""NOT FOUND"""
.FormatConditions(1).Interior.ColorIndex = 3
End With
Range("I16").Select
End Sub
回答by Lance Roberts
Here's an ugly brute-force approach:
这是一个丑陋的蛮力方法：
Dim r As Range
Dim s As Range
For Each r In ActiveSheet.UsedRange.Rows
For Each s In Sheets("Sheet2").UsedRange.Rows
If r.Cells(1, 1).Value = s.Cells(1, 1).Value Then
r.Interior.ColorIndex = 3
End If
Next s
Next r
Here's a slicker way:
这是一个更巧妙的方法：
Dim r As Range
Dim s As Range
Set s = Sheets("Sheet2").Columns(1)
For Each r In ActiveSheet.UsedRange.Rows
If Not (s.Find(r.Cells(1, 1).Value) Is Nothing) Then
r.Interior.ColorIndex = 3
End If
Next r
