Need help with VBA Code

rubyh

Board Regular
Joined
Mar 31, 2006
Messages
213
i need help with this Code
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]
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Hi

Code:
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

Board Regular
Joined
Mar 31, 2006
Messages
213
Sorry this code provides a better understanding

Code:
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
 

Forum statistics

Threads
1,141,761
Messages
5,708,367
Members
421,566
Latest member
7Nabisco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top