rubyh
Board Regular
- Joined
- Mar 31, 2006
- Messages
- 213
i need help with this Code
What im trying to do is compare the information in these two workbooks and highlite the rows that are not found in Database.xls. however the workbook APS.xls contains multiple worksheets. so i need the code to search though all the worksheets in APS.xls and if it doesnt find a match to hilight the rows that are not found in Database.xls.. please hlp ive been wracking my brain for 3 days! with this code[/quote]
Code:
Sub ChckNotADDED()
Dim wb1 As Workbook, wb2 As Workbook
Dim dic As Object, r As Range, wsw As Worksheet
Set wb1 = Workbooks("APS.xls")
Set wb2 = Workbooks("Database.xls")
Set dic = CreateObject("scripting.dictionary")
dic.comparemode = vbTextCompare
With wb2
For Each ws In .Worksheets
With ws
For Each r In .Range("a2", .Range("a" & Rows.Count).End(xlUp))
If Not IsEmpty(r) Then
If Not dic.exists(r.Value) Then
dic.Add r.Value, Nothing
End If
End If
Next
End With
Next
End With
With wb1
.Cells.Interior.ColorIndex = 0
For Each r In .Range("h2", .Range("h" & Rows.Count).End(xlUp))
If Not dic.exists(r.Value) Then
r.EntireRow.Interior.ColorIndex = 4
End If
Next
End With
Set wb1 = Nothing: Set wb2 = Nothing
Set dic = Nothing
End Sub