Search across all Tabs for match

[bradjedis]

Hi folks,

Issue: I have a list of data (mixed characters) with 90+ tabs.

Need to search the entire workbook for matches to the list. IF found, highlight the number in the list. Do not need to search formulas.



List is approx. 400, data in tabs is variable in length and location of data.


Thanks,
Brad

[HansV]

What exactly do you want to match to what?

[bradjedis]

If any of the numbers in the list of data appears in any of the other tabs in the workbook, then highlight the cell in the initial list.

[HansV]

Activate the Visual Basic Editor.
Insert a module.
Copy/paste the following function into the module:

Code: Select all

Function MatchValue(rngCell As Range) As Boolean
  Dim wsh As Worksheet
  Dim rngfind As Range
  If rngCell.Value = "" Then
    Exit Function
  End If
  For Each wsh In Worksheets
    If wsh.Name <> rngCell.Parent.Name Then
      Set rngfind = wsh.Cells.Find(What:=rngCell.Value, LookIn:=xlValues, LookAt:=xlWhole)
      If Not rngfind Is Nothing Then
        MatchValue = True
        Exit Function
      End If
    End If
  Next wsh
End Function

Switch back to Excel.
Select the list of data. Let's assume that B2 is the active cell within the selection.
Select Format | Conditional Formatting... (Excel 2003 or earlier) or click Conditional Formatting | New Rule... in the Home tab of the ribbon (Excel 2007 or later).
Select 'Formula Is' from the first dropdown (Excel 2003 or earlier) or 'Use a formula to determine which cells to format' (Excel 2007 or later).
Enter the following formula in the box that appears:

=MatchValue(B2)

where B2 is, as mentioned above, the active cell within the selection.
Click Format... and specify the formatting that you want.
OK your way out.

[bradjedis]

Thanks I shall give it a go.

Will reply with results.

Brad