Perform multiple Search at the same time

June 11, 2013 at 23:51:23
Specs: Windows 7

I wan to perform multiple search of different values at the same time

A workbook contains 3 sheets
Sheet1 Contains the values I need to search in Sheet 2 and Sheet 3
The Search should perform in such a manner that After pressing Ctrl+F it should automatically accepts the values from sheet 1 one by one search the whole workbook except sheet1 and if the value is found in any other sheet it should highlight the found value and then returns to sheet1 and accept the second value performs the search and return to sheet 1 an so on..
and display the success rate.

See More: Perform multiple Search at the same time

August 15, 2013 at 04:27:05

On sheet 1 which column will the search values appear?

my assumptions
Sheet1 column A will contain the search values
sheet2 column A will be the searched column
Sheet3 column A will be the searched column

So with those assumptions here is a quick macro that will look at each value in column A in sheet 1, and compare it to the values in column A in sheet 2 and 3. If the values match it will highlight the cell in sheet 2 and 3 red.

Note: to run this you will need to copy paste into a Module then hit F5 while your cursor is on the "Sub SearchForSomething" or just hit F5 and then click Run

This may not be the neatest way to doing it but it works for me.

Public SearchVal As String

Sub SearchForSomething()
    Dim URange, LRange
    Dim BCell As Excel.Range
    Set URange = Sheet1.Range("A1")
    Set LRange = Sheet1.Range("A" & Rows.Count).End(xlUp)
    For Each BCell In Range(URange, LRange)
        SearchVal = BCell.Value
        SearchSheets "Sheet2"
        SearchSheets "Sheet3"
    Next BCell

End Sub

Sub SearchSheets(nSheet)
    Dim URange, LRange
    Dim BCell As Excel.Range
    Set URange = Sheets(nSheet).Range("A1")
    Set LRange = Sheets(nSheet).Range("A" & Rows.Count).End(xlUp)
    For Each BCell In Range(URange, LRange)
        If BCell.Value = SearchVal Then
            BCell.Interior.Color = vbRed
        End If
    Next BCell
End Sub

hope this helps start you off

Report •

August 15, 2013 at 09:33:17
If you do not want to use Macros, you can accomplish the same thing
with a Defined Name and Conditional Formatting and everything happens
automatically, as soon as you enter your search string.

Try this:

Sheet 1 cells A1 thru A10 will contain the search values
and will be given a Defined Name:

On Sheet 1
Select cells A1 tru A10
Select Formula Tab
Select Define Name
In the Name Box enter a name like MyList
Make sure the Scope box reads: Workbook
and the Refers To Box shows: =Sheet1!$A$1:$A$10
Click OK

On Sheet 2 cells A1 thru C10 these cells may contain the search values
and will use Conditional Formatting:

Select cell A1 thru C10
Select Home Tab
Select Conditional Formatting
Select New Rule
Select Use Formula to determine which cells to format
(last selection in the ⊤op box)

In the Formula Box enter the formula:


Select the Format Button
Select the Fill Tab
Select a pretty color
Click OK

Repeat for Sheet 3

Now when the Search string is entered in any of the cells on Sheet 1, cells in A1 tru A10
if the corresponding string is found on Sheet 2 or 3 in cells A1 thru C10 they will be highlighted.

I would advise against using whole columns as your ranges, as it could slow things down.
Use only those ranges that you will be using.


Report •

August 15, 2013 at 10:23:47
Another macro option is shown below.

Using .Find and .FindNext is usually more efficient than looking at each cell one by one and comparing one value to another. Similar to Find in Excel, VBA Find goes directly to the next occurrence of the search string. For a long list of values to check, .Find can be a lot quicker.

Sub ColorMeRed()
Dim lastRow, shtNum As Long
Dim firstAddress As String
Dim c
'Determine length of Search list on Sheet1 Column A
lastRw = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
'Loop through Sheets
  For shtNum = 2 To Sheets.Count
'Loop through Search list
    For Each cell In Sheets(1).Range("A1:A" & lastRw)
'Find search value in each sheet
     With Sheets(shtNum).Range("A1:A" & Sheets(shtNum). _
                         Range("A" & Rows.Count).End(xlUp).Row)
      Set c = .Find(cell, lookat:=xlWhole)
'Color it Red each time it's found
       If Not c Is Nothing Then
        firstAddress = c.Address
            c.Interior.Color = vbRed
            Set c = .FindNext(c)
          Loop While Not c Is Nothing And c.Address <> firstAddress
       End If
     End With
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •
Related Solutions

Ask Question