Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Nov 2014
    Posts
    6
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Highlight specific text in a search

    Hi

    Ill try to see if this comes across ok.

    I have built a dashboard in excel.
    The sheet "returned Results" has many text boxes all assigned to different macros.
    Each macro basically looks at a specific tab, and find rows of data where there is a keyword match to whatever i type into cell a1 on sheet "Returned Results" and then brings back the whole row from the searched tab and copies into "Returned results starting at a specific row.
    Its a kind of knowledge base.

    It works perfectly but what i would like to do now, but without success is to highlight on the rows that have been returned, the keyword that has been typed into cell a1.

    So, if i type in the word Brush, into cell a1 on the sheet "Returned Results" and run the macro, it will look at the tab it searches from and return all rows of data where the word brush was found. Now i am hoping for the word "brush" to be highlighted in the returned results so the person using it, doesnt have to spend so much time reading each of the cell contents to see where the word is.

    I have found a few solutions, but most refer to an input box where as i would like the macro to say look at cell a1 in the returned results tab as its search string target.

    Any ideas, and thanks in advance for any help.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Matt,

    You can use conditional formatting to accomplish this:
    MattCondFmt.JPG

    Setup:
    1. Assign a RangeName to Cell A1
    2. Highlight the range of cells where the rows will be returned
    3. Create a Conditional Formatting rule using a formula that references the assigned range name and the 1st cell in the highlighted range.

    HTH

    Test File: MattCondFmt.xlsx
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    CACI Matt (2014-11-13)

  4. #3
    New Lounger
    Join Date
    Nov 2014
    Posts
    6
    Thanks
    4
    Thanked 0 Times in 0 Posts
    HI Retired Geek ( Nice name :-))

    This looks like it highlights the whole cell rather than the text within a cell.
    So if my retruned resuts were "use a large paint brush" rather than highlihting the word brush, it highlights whle whole cell.
    Is there a way to highlight jusst the cell text based upon my seatch word in cell A1?

    Many Thans for your reply and suggestion.

  5. #4
    New Lounger
    Join Date
    Nov 2014
    Posts
    6
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Hi all I found this on Ron De Bruins website. It works a treat, but i need it tweaked so that it only highlights the search word text, not all of the text in the applicable cell and so it looks at cell a1 an not an array you type into the macro.

    Sub Color_cells_In_Range_Or_Sheet()
    Dim FirstAddress As String
    Dim MySearch As Variant
    Dim myColor As Variant
    Dim Rng As Range
    Dim I As Long

    'Fill in the search Value and color Index
    MySearch = Array("ron")....................NEED THIS TO SHOW AS CELL A1 ON SHEET1

    With Sheets("Sheet1").Range("a3:bl5000")

    'Change the font in the column to Automatic
    .Font.ColorIndex = 1

    For I = LBound(MySearch) To UBound(MySearch)

    'If you want to find a part of the rng.value then use xlPart
    'if you use LookIn:=xlValues it will also work with a
    'formula cell that evaluates to MySearch(I)
    Set Rng = .Find(What:=MySearch(I), _
    After:=.Cells(.Cells.Count), _
    LookIn:=xlFormulas, _
    LookAt:=xlPart, _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, _
    MatchCase:=False)

    If Not Rng Is Nothing Then
    FirstAddress = Rng.Address
    Do
    Rng.Font.ColorIndex = myColor(I)
    Set Rng = .FindNext(Rng)
    Loop While Not Rng Is Nothing And Rng.Address <> FirstAddress
    End If
    Next I
    End With
    End Sub


    NEED IT TO ONLY COLOUR THE SEARCH WORD NOT ALL OF THE TEXT IN THE CELL WHERE THE SEARCH WORD WAS FOUND.
    woops shouty....... i have been playing fpr about an hour and have made a few changes to how it colours the fone etc but cant crack this last bit.

    thanks you all

  6. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    CACI,

    Here is a search routine I wrote a while back but adjusted it to highlight the keyword. See if something like this would work for you.

    In the search box (yellow), type your keyword. The rows will be filtered as you type according to the keyword and the matching keyword(s) in the filtered rows will have a red font. Additional code can be added to take the selected result row and place it where you want. This can easily be adapted to just about any spreadsheet.

    HTH,
    Maud

    Search1.png

    Search2.png

    Code:
    Private Sub TextBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
        On Error Resume Next
        Application.ScreenUpdating = False
    '----------------------------------------------------
    'DECLARE AND SET VARIABLES
        Dim LastRow As Integer, KeyLength As Integer, num As Integer
        LastRow = ActiveSheet.Cells(Rows.Count, 7).End(xlUp).Row
        KeyLength = Len(TextBox1.Value)
    '----------------------------------------------------
    'RESET FONT TO BLACK
        For I = 1 To LastRow
            Cells(I, 7).Font.Color = vbBlack
        Next I
    '----------------------------------------------------
    'FILTER ROWS AND CHANGE FONT COLOR BASED ON CRITERIA
        If TextBox1.Value = "" Then
            ActiveSheet.UsedRange.AutoFilter Field:=7, Criteria1:=Null  'TAKE CARE OF BLANK CELLS IN FILTERED COLUMN 7
        Else:
            ActiveSheet.UsedRange.AutoFilter Field:=7, Criteria1:="*" & TextBox1.Value & "*"
        End If
        For I = 2 To LastRow
            If Cells.Rows(I).Hidden = True Or KeyLength = 0 Then GoTo Continue
            num = WorksheetFunction.Search(TextBox1.Value, Cells(I, 7), 1)
            With Cells(I, 7).Characters(Start:=num, Length:=KeyLength).Font
                .Color = vbRed
            End With
    Continue:
        Next I
        Application.ScreenUpdating = True
    End Sub
    Addendum: The above code gets placed in the worksheet's module. You will note by the macro's title that you are actually entering the keyword into a textbox on the sheet not a cell
    Attached Files Attached Files
    Last edited by Maudibe; 2014-11-13 at 18:14. Reason: added file

  7. The Following 2 Users Say Thank You to Maudibe For This Useful Post:

    CACI Matt (2014-11-14),Nicole545 (2014-11-15)

  8. #6
    New Lounger
    Join Date
    Nov 2014
    Posts
    6
    Thanks
    4
    Thanked 0 Times in 0 Posts
    OMG that is brilliant, Thank you. It does exactly what i want, but are we able to change it so the macro looks at what is typed into Cell a1, rather than a text box.
    Thank you again so much.

  9. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Matt

    in my attached file, text on the sheet will be searched for the text entry in cell [A1].
    The text will be highlighted with the colour of the text assigned in cell [A1].

    ..but note, only the FIRST matching entries within a cell will be highlighted.
    If a cell contains the search word (or phrase) more than once, then the routine will need to be updated to repeat the highlighting of multiple entries within a single cell.

    zeddy
    Attached Files Attached Files

  10. The Following User Says Thank You to zeddy For This Useful Post:

    CACI Matt (2014-11-14)

  11. #8
    New Lounger
    Join Date
    Nov 2014
    Posts
    6
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Zeddy

    That is perfect. Thank you ever so much, and thank you everyone for your help and suggestions. I have saved all the macros as they will all come in handy with various projects i am working on.

    Matt

  12. #9
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    Nicely done. Only comment is use native instr instead of application.find
    InStr(zRng, ztext)

  13. The Following User Says Thank You to Supershoe For This Useful Post:

    zeddy (2014-11-19)

  14. #10
    New Lounger
    Join Date
    Nov 2014
    Posts
    21
    Thanks
    16
    Thanked 1 Time in 1 Post
    Maudibe,

    That is so clever!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •