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.
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.
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