Need help with VBA Code

[rubyh]

i need help with this 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

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]

 

[acw]

Hi

With wb1 
  for each ws in .worksheets
   with ws
     .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 
  next ws
end with

Tony

 

[rubyh]

Sorry this code provides a better understanding

Sub ChckNotADDED()
Dim wb1 As Workbook, wb2 As Workbook
Dim dic As Object, r As Range, wsw As Worksheet
Set wb1 = Workbooks("Database.xls")
Set wb2 = Workbooks("APS.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
  For Each ws In .Worksheets
   With ws
     .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

I get a compile error code at END SUB

 

[rubyh]

I GOT IT!!!!!! YOHAAAAAAAA im happy thanks

 

[Macropheliac]

You're missing an End With and a Next at the end of the code.