How to search in the current workbook

Hi all,

I am looking for a way to have a macro look in the current workbook (not a specific one, but the one that is open at the moment) to look for values that aren't in another workbook. I have the following code which works for a specific workbook, but instead of "List Copy" I want the macro to compare whatever file is open now. It will most likely be the List Copy with a different date.

Sub colorlist()
Dim SearchRange As Range
Dim NUmberRange As Range
Dim Searchvalue As String
Dim xCell As Variant
Dim c As Range

'Where is the search list?
Set SearchRange = Workbooks("Bill of mat.xlsm").Worksheets("Database").Range("A2:A5000")
'Where is list of numbers?
Set NUmberRange = Workbooks("List Copy.xlsm").Worksheets("Sheet1").Range("E2:E100000")

For Each c In NUmberRange
Searchvalue = c.Value

On Error Resume Next
Set xCell = SearchRange.Find(what:=Searchvalue, matchbyte:=False)
On Error GoTo 0
If xCell Is Nothing Then
c.Interior.ColorIndex = 3
Else
c.Interior.ColorIndex = 0
End If
Next c

ActiveSheet.Range("$A$1:$Z$65745").AutoFilter Field:=5, Criteria1:=RGB(255 _
, 0, 0), Operator:=xlFilterCellColor
End Sub

Originally Posted by AlphaFrog

This will reference the Active workbook at the time the macro is run.
Set NUmberRange = ActiveWorkbook.Worksheets("Sheet1").Range("E2:E100000")

This will reference the last workbook opened.
Set NUmberRange = Workbooks(Workbooks.Count).Worksheets("Sheet1").Range("E2:E100000")

Thanks, can you elaborate on how to use the second one? I put it in but it gives me a rune time error "9".

Originally Posted by AlphaFrog

Replace this...
Set NUmberRange = Workbooks("List Copy.xlsm").Worksheets("Sheet1").Range("E2:E100000")
With This...
Set NUmberRange = Workbooks(Workbooks.Count).Worksheets("Sheet1").Range("E2:E100000")

Make sure the last opened workbook has a worksheet named "Sheet1". Or use this to reference its 1st sheet regardless of the sheet name.
Set NUmberRange = Workbooks(Workbooks.Count).Worksheets(1).Range("E2:E100000")

Ah okay I got it. But I want to change the initial code. In my original code "List copy" had all the original values and the macro was searching "bill of mat" and showing in "List Copy" what values weren't in "bill of mat". The macro was placed on my computer, but now I would like to place it in the "bill of mat" workbook so anyone who has that file can use it. I tried switching around the workbook names in the code, but it isn't working. Can you help me solve this?

Originally Posted by AlphaFrog

I don't really follow what that means.

You may want to use the ThisWorkbook keyword. It refers to the workbook that contains the macro regarless of the workbook's name.

So this...
Set SearchRange = ThisWorkbook.Worksheets("Database").Range("A2:A5000")
...will reference the workbook that has the macro code.

Is that what you want?

I'm sorry for the confusion, let me restate in a better manner.

In my intial post I was running the macro from my computer, it wasn't in any specific workbook. It was checking numbers in "Bill of Mat" and comparing them with a list in "List Copy" and then highlighting any cells that weren't in "Bill of Mat". This worked for me, but it wouldn't work if someone else wanted to do it since it was not in any of the workbooks.

Now, I would like to alter the code so that the macro can be embedded in the "Bill of Mat" workbook and have it look up the list of numbers and compare it against "List Copy" (which I have decided to use the index code so that it will always be the second one open), and still highlight the ones that are missing from "Bill of Mat" in "List Copy". I want to use the index code, because the "List of Copy" file name will always change as it has a date at the end of it which will change day from day.

I have bolded the parts I changed. I apologize for any miscommunication.

Sub colorlist()
Dim SearchRange As Range
Dim NUmberRange As Range
Dim Searchvalue As String
Dim xCell As Variant
Dim c As Range

'Where is the search list?
Set SearchRange = Workbooks(2).Worksheets("Sheet1").Range("E2:E100000")
'Where is list of numbers?
Set NUmberRange = Workbooks("S_BOM.xlsm").Worksheets("Database").Range("A2:A10000")
For Each c In NUmberRange
Searchvalue = c.Value

On Error Resume Next
Set xCell = SearchRange.Find(what:=Searchvalue, matchbyte:=False)
On Error GoTo 0
If xCell Is Nothing Then
c.Interior.ColorIndex = 3
Else
c.Interior.ColorIndex = 0
End If
Next c

ActiveSheet.Range("$A$1:$Z$100000").AutoFilter Field:=5, Criteria1:=RGB(255 _
, 0, 0), Operator:=xlFilterCellColor


Range("A1:Z100000").Select
ActiveSheet.Range("$A$1:$Z$65745").RemoveDuplicates Columns:=5, Header:= _
xlYes
End Sub

Originally Posted by AlphaFrog

I think I better understand except for one part. It looks like your original code is opposite from the newest code.

Original code:

Please Login or Register  to view this content.

SearchRange is in "Bill of mat"
NUmberRange is in "List Copy"

Newest code:

Please Login or Register  to view this content.

SearchRange is in 2nd workbook (List Copy ?)
NUmberRange is in "S_BOM.xlsm" (Bill of mat?)

Are the workbooks swapped?

I swapped them because I thought I would have to because I was now looking up the numbers from "Bill of Mat" (Sbom) not the Copy List

Originally Posted by AlphaFrog

It looks like it should work then. What part doesn't work? Does it error? If yes, what's the error and what line is highlighted? Need specifics.

It doesn't error. I just hit run and it runs for a few seconds and when its done neither of the workbooks change.

Originally Posted by AlphaFrog

Did you open "S_BOM.xlsm" first and the other workbook (List Copy?) second?
Do any cells get colored?

Can you attach your two workbooks? Paperclip icon on the "Advanced" form editor menu.

Yes I opened them in that order, nothing gets colored. I'll have to make some dummy files and upload them. I'll do it right now.

Originally Posted by XeRo Solus

Instead of swapping them you could moved the previous code you had to a personal.xlsb

Can you please elaborate, I'm not quire sure I'm understanding what you're saying. Sorry.

Originally Posted by AlphaFrog

This does not address why the code doesn't work.

Okay, so I have attached the two excel sheets.

Originally Posted by AlphaFrog

Your original macro code (regardless of workbook names) doesn't work with the example data you provided. So the example pseudo-data was not helpful. You had stated the original code worked except that you wanted to adjust for workbook names. I thought that's what your question was.

Just visually looking at the pseudo-data, it appears all parts in BOM match the List of Copy. So are they all suppose to be red? A description there could have helped. If yes, swap your two ColorIndex values.

Below is just a guess because I don't really understand what you want. The fix is likely quite simple. I'm spending most of my time just trying to divine the question.

Put this code in the BOM workbook.

Please Login or Register  to view this content.

I'm really sorry for the miscommunication on my part. Let me just state what I would like to have happen. I would like to compare the two lists "bill of mat" and "List of Copy". In bill of mat I have parts 1 & 2, but not 3 & 4 which are in "List of Copy". So, I would like the missing values from "Bill of Mat" to be highlighted in "List of Copy". The part that is messing me up is that I would like to run the macro from the "Bill of Mat" workbook.

Hopefully I have been more precise. I really do appreciate your persistance and I apologize for the lack of details from my side.

Originally Posted by AlphaFrog

Please Login or Register  to view this content.

It worked!!! Thank you so much, I really appreciate the help and persistance. I promise I'll be clearer in my description next time.

NVM figured it out.