Lookup on spreadsheets

  steviegee 08:34 02 Dec 2008

I have 2 spreadsheets and I want to know how to see if various numbers are on both sheets. Vlookup won't really work as the numbers could be all over the place on both sheets. I want to select one spreadsheet and if that number is anywhere on the other spreadsheet it is highlighted or something. Thanks.

  VoG II 12:40 02 Dec 2008

Perhaps VLookAllSheets click here

  steviegee 13:32 02 Dec 2008

Thanks VoG™ but I dont have full internet access so will have to look when I get home. But Vlookup only looks in one column doesnt it where these numbers could be absolutely anywhere on the sheet.

  VoG II 17:41 02 Dec 2008

Try this: press ALT + F11 to open the Visual Basic Editor then Insert > Module and paste in the following code

Sub steviegee()
Dim LookRng As Range, Found As Range
On Error Resume Next
Set LookRng = Application.InputBox("Click in cell to search for value", Type:=8)
On Error GoTo 0
If LookRng Is Nothing Then Exit Sub
Set Found = Sheets("Sheet2").UsedRange.Find(what:=LookRng.Value)
If Not Found Is Nothing Then Found.Interior.ColorIndex = 3
End Sub

Change Sheet2 to the name of your second sheet. Close the VBE using the X.

With your first sheet selected, Tools > Macro > Macros, highlight steviegee and click the run button.

  steviegee 08:48 03 Dec 2008

Doesnt seem to be working. What is supposed to happen? Does it highlight the numbers in sheet 2 that are not in sheet 1?

  VoG II 14:27 03 Dec 2008

If the number that you select on sheet1 is found on sheet2 the number on sheet2 will be coloured red.

  steviegee 14:35 03 Dec 2008

Really what I need is any numbers that are on sheet 2 but not on sheet 1 then highlighted on sheet 2!

  VoG II 15:05 03 Dec 2008


Sub steviegee2()
Dim c As Range, Found As Range
With Sheets("Sheet2")
For Each c In .UsedRange
Set Found = Sheets("Sheet1").UsedRange.Find(what:=c.Value)
If Found Is Nothing Then c.Interior.ColorIndex = 3
Next c
End With
End Sub

  steviegee 08:32 04 Dec 2008

It seems to work although it is taking forever. The hour glass has been there for ages and it seems to be doing nothing (however both sheets have a lot of data). Is it a long process?

  VoG II 11:45 04 Dec 2008

It will take ages because it looks at each value on sheet2 and checks for that value in sheet1.
I don't know another approach to this if the numbers can be literally anywhere in both sheets.

  steviegee 11:52 04 Dec 2008

Thanks it has worked now. Out of curiosity of what significance is steviegee in the formula as neither worksheet are titled this.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Xiaomi Mi Mix 2 review

What went wrong at the Designs of the Year 2017

iPhone X news: Release date, price, new features & specs

Comment utiliser Live Photos ?