Compare sheets in workbook

JTPhillips,

We can use a macro to:

1. create a list on a new worksheet Results?

2. highlite the name on the inception sheet if it is not on the current sheet? If so, what background color?

3. use a column on the inception sheet, title Not In Current, and put in the cell NOT? You could then filter on column G?

What results are you looking for?

Hi JTPhillips,

This should put a Tilde in Column G if there is no counterpart on the other sheet - but I didn't find any!

=IF(LOOKUP(A2,June2011_current!A:A)<>"","","~")

Sorry, formula giving incorrect results

JTPhillips,

There are two hidden worksheets, Sheet2, and Sheet3, in the workbook?

Are these sheets being used by other macros?

Corrected formula in conditional formatting (as far as I can see), forgot i needed a named range

=COUNTIF(TESTME,A2)<1

TESTME is a named range on sheet "june11_current" from A2 to A4007, you need to use a named range if using a different sheet (which is what I neglected on the first attempt)

JTPhillips,


Detach/open workbook Compare2Sheets - w1 w2 - JTPhillips - EF838119 - SDG10.xlsm and run the Compare2Sheets macro.



If you want to use the macro on another workbook:


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub Compare2Sheets()
' stanleydgromjr, 06/13/2012
' http://www.excelforum.com/excel-programming/838119-compare-sheets-in-workbook.html
Dim wi As Worksheet, wc As Worksheet
Dim r As Long, lr As Long, fr As Long
Application.ScreenUpdating = False
Set wi = Worksheets(1)
Set wc = Worksheets(2)
lr = wi.Cells(Rows.Count, 1).End(xlUp).Row
With wi.Range("A2:A" & lr)
  .Interior.Pattern = xlNone
  .Interior.TintAndShade = 0
  .Interior.PatternTintAndShade = 0
End With
For r = 2 To lr Step 1
  fr = 0
  On Error Resume Next
  fr = Application.Match(wi.Cells(r, 1), wc.Columns(1), 0)
  On Error GoTo 0
  If fr = 0 Then wi.Cells(r, 1).Interior.Color = 255
Next r
Application.ScreenUpdating = True
End Sub

Before you run the macro on another workbook, make sure that there are no hidden worksheets. And, that the left most worksheet is inception_5.31.11, or its equivalent, and that the workbook to its right is June2011_current, or its equivalent.


Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the Compare2Sheets macro.

JTPhillips,

Thanks for the feedback.

You are very welcome. Glad I could help.

Come back anytime.