Search and highlight only containing text

I have a data base sheet...now i want to find the text,string,word into the database sheet and want the results to search-engine sheet..i can do this with the help of advanced filter but now i m not able to highlit that text what i find in the search criteria cell...please help

Highlighting text WITHIN a cell string isn't standard, highlighting the cell itself is.

Update your example workbook. Include:

1) the macro as you have it so far
2) before/after examples
3) point out where things are going on if not obvious in the macro or on the sheet

Remember, we don't know what you're after unless you present the need completely.

This is the amended code for your SEARCH button:

Private Sub Search_Click()
Dim LR As Long, Val As String, cell As Range, Rng As Range
Dim dSh As Worksheet, v

'Setup
    Application.ScreenUpdating = False
    Val = Range("E2").Value
    Set dSh = Sheets("DATABASE")
    Range("A9:F200").ClearContents

'Transfer matching data
    LR = dSh.Range("A" & Rows.Count).End(xlUp).Row
    dSh.Range("A2").AutoFilter
    dSh.Range("A2").AutoFilter Field:=1, Criteria1:="=*" & Val & "*"
    dSh.Range("B2:F" & LR).SpecialCells(xlCellTypeVisible).Copy Range("B9")
    dSh.Range("A2").AutoFilter

'Color matching strings
    LR = Range("B" & Rows.Count).End(xlUp).Row
    Set Rng = Range("B10:F" & LR)

    For Each cell In Rng
        v = InStr(cell.Text, Val)
        If v > 0 Then
            cell.Characters(Start:=v, Length:=Len(Val)).Font.ColorIndex = 3
            v = 0
        End If
    Next cell

Application.ScreenUpdating = True
End Sub

The version I already gave you works for searching by vendor name or item # or whatever you want to use. The stars aren't necessary either. Just use the one I already gave you and enter a vendor name or item #, it will work fine.

No need to further restrict, it's ready to use.

Sorry, I don't see you have a problem here. It's ready to use.

It seems to me then that based on the original layout where we are looking in column A where you had done the concatenation formula, you can just change the formula in that column to ONLY include the Item # and Vendor names and that should restrict it down the way you indicated.

Provide an updated sheet with where you are now, include the macro and the changed column A concatenation formula and realistic search example parameters and I'll take a look.

You didn't change the concatenation formula in DATABASE column A like I told you. =B3&C3 is all you need in A3, then copy down. This eliminates the information to search other columns, right?

Here's a new version that gives you the Vendor or Item code only search ability on sheet 3.

1) It no longer uses column A on Sheet1 at all. Depending on which search code you enter, it searches by THAT column on sheet1.

2) The ITEM CODES on sheet1 need to be TEXT STRINGS for this to work properly. The easiest way to convert an existing numeric value to text string is to use this in an adjacent cell:

=B3&""

Then copy that down the whole dataset to get the text values, then copy them and EDIT > PASTESPECIAL > VALUES over the original numbers to get a new set of text values instead.

In the future, make sure the column is already formatted as text before entering the numbers and they will be accepted by Excel as text.

3) Sheet3 now auto clears if you delete both search criteria

4) Since only one search criteria is needed, the other cell will clear itself if you enter a value in the other search cell

5) The VENDOR search will capitalize for you even if you enter "auto"...it becomes "AUTO".

6) As soon as you enter a search string, the search will occur, no need to click a button.

7) The CLEAR and SEARCH buttons aren't really needed anymore

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].


(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)