查找在表1中存在但不在表2中的缺失值
查找在表1中存在但不在表2中的缺失值
提问于 2019-02-04 00:34:03
我编写了一个示例代码，它比较两个工作表，如果在sheet1 for col1中没有在sheet2 for col2中找到值，则突出显示不同的值。
我想把这个代码扩展到整张纸上。因此，任何不在表2中的值都在表1中突出显示。
我怎么能这么做？我试图增加A:Z的范围，但它给了我错误
Sub CompareAndHighlight()
Dim rng1 As Range, rng2 As Range, i As Integer, j As Integer
Dim isMatch As Boolean
For i = 2 To Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
isMatch = False
Set rng1 = Sheets("Sheet1").Range("A" & i)
For j = 1 To Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
Set rng2 = Sheets("Sheet2").Range("A:B" & 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)
End If
Set rng1 = Nothing
Next i
End Sub回答 2
Stack Overflow用户
回答已采纳
发布于 2019-02-04 02:12:27
Sub CompareAndHighlight()
Dim xRange As Range, yRange As Range
Dim xCell As Range, yCell As Range
Dim Found As Range
Dim wsX As Worksheet: Set wsX = ThisWorkbook.Sheets("Sheet1")
Dim wsY As Worksheet: Set wsY = ThisWorkbook.Sheets("Sheet2")
LR1 = wsX.Range("A" & wsX.Rows.Count).End(xlUp).Row
LR2 = wsY.Range("B" & wsY.Rows.Count).End(xlUp).Row
Set xRange = wsX.Range("A1:A" & LR1)
Set yRange = wsY.Range("B1:B" & LR2)
For Each xCell In xRange
Set Found = yRange.Find(xCell.Value)
If Found Is Nothing Then
xCell.Interior.Color = RGB(255, 0, 0)
End If
Set Found = Nothing
Next xCell
End SubStack Overflow用户
发布于 2019-02-04 02:22:55
试试这个：
Sub CompareAndHighlight()
' Declare object variables
Dim originalRange As Range
Dim compareToSheet As Worksheet
Dim cellRange As Range
' Declare other variables
Dim originalSheetName As String
Dim compareSheetName As String
' >>> Customize
originalSheetName = "Sheet1"
compareSheetName = "Sheet2"
' Initiate objects
Set originalRange = ThisWorkbook.Worksheets(originalSheetName).UsedRange
Set compareToSheet = ThisWorkbook.Worksheets(compareSheetName)
' Loop through used cells in first sheet
For Each cellRange In originalRange
If StrComp(cellRange.Value, compareToSheet.Range(cellRange.Address).Value, vbTextCompare) <> 0 Then
cellRange.Interior.Color = RGB(255, 0, 0)
End If
Next
End Sub页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/54508987
复制相关文章
相似问题
腾讯云开发者
