Solved

MS Excel Search Find and Highlight row

Posted on 2014-04-07
8
1,890 Views
Last Modified: 2014-04-24
Hello,
I may be asking too much on this question, yet allow me to ask just in case there is an Expert that might know.
I'm working with several Excel Wookbooks.  I am hoping to be able to make a search, and when it find my search highlight the row, or rows.  I can now replace the search with a different font etc, but being able to highlight the row would be nice.

Might this be an option with a newer version of Excel?
0
Comment
Question by:chima
  • 4
  • 3
8 Comments
 
LVL 19

Assisted Solution

by:helpfinder
helpfinder earned 50 total points
Comment Utility
you can use feature to highlight the cell or more cells in a row

highlight
also you can use conditional formatting to automatically highligth cell containing text/number you are looking for
0
 
LVL 26

Expert Comment

by:MacroShadow
Comment Utility
Once the cells are found under "Find all" you can SHIFT + down arrow in the
"found" dialog box which selects all the found cells on the sheet.

Using a macro:
Sub HighlightCells()
    

Dim i As Long
Dim Fnd As String
Dim fCell As Range

Fnd = InputBox("Enter text!", "Search text")

    Set fCell = Range("A1")

        For i = 1 To WorksheetFunction.CountIf(ActiveSheet.UsedRange, Fnd)
            Set fCell = Cells.Find(What:=Fnd, After:=fCell, _
                LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
                SearchDirection:=xlNext, MatchCase:=False)
                
            If fCell Is Nothing Then
            Msgbox fnd & " not on sheet !!"
            Exit Sub
            Else
             With fCell
                 .Interior.ColorIndex = 6
             End With
            End If
            
        Next i
End Sub

Open in new window


from: http://www.excelforum.com/excel-programming-vba-macros/646857-highlight-selected-cell-when-using-find.html
0
 

Author Comment

by:chima
Comment Utility
MacroShadow, thank you for the help.  I have not gotten into VB, yet I am very eager to do so.
I got this far, and if you do not mind, I have a couple of questions.  This may be premature questions because I am reading from sites like; http://office.microsoft.com/en-us/excel-help/create-or-delete-a-macro-HP010014111.aspx#BMassignmacro  to further my understanding.

Question; I have your code in VB and I could execute it (run it), and while it appears that the code needs further development, I would like to know how could I embed your code into the Excel sheet I have open?
You will see from the screenshot that it found the first word, but not the second.  Also it highlighted the cell, and was hoping to highlight the entire row.
Getting the code from VB to the Excel sheet.
0
 

Author Comment

by:chima
Comment Utility
Update: I got the macro to appear at the Excel's Macro list, where I can select it and run it.  I assigned Ctl+w to run it, but it is not working.
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 26

Expert Comment

by:MacroShadow
Comment Utility
You're right it only highlights the cell, this version will highlight the entire row.

If you would search for *Great it would highlight both rows.
You can use any of the wildcards for the search (? will find any one character, * will find any number of characters, # will find any one number, [ ] will match any one of the characters specified within the brackets, [!] will match any one character not specified within the brackets)

Public Sub HighlightCells()


    Dim i As Long
    Dim Fnd As String
    Dim fCell As Range

    Fnd = InputBox("Enter text!", "Search text")

    Set fCell = Range("A1")

    For i = 1 To WorksheetFunction.CountIf(ActiveSheet.UsedRange, Fnd)
        Set fCell = Cells.Find(What:=Fnd, After:=fCell, _
                               LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
                               SearchDirection:=xlNext, MatchCase:=False)

        If fCell Is Nothing Then
            MsgBox Fnd & " not on sheet !!"
            Exit Sub
        Else
            With fCell.EntireRow
                .Interior.ColorIndex = 6
            End With
        End If

    Next i
    
End Sub

Open in new window

0
 

Author Comment

by:chima
Comment Utility
MacroShadow,  I have not tried your last code revision, yet allow me to ask you a quick question.  I am working with an Excel file that is password protected.  When I try to apply any other macro and VB come up to do so, I get a log in window.  Obviously this means apply the password.  My question is; Is there a way of applying my macro beside/over the existing protection?  thanks
0
 
LVL 26

Accepted Solution

by:
MacroShadow earned 450 total points
Comment Utility
The only way to add code to a password protected project, is with the password. Actually it is quite easy to crack it, but I don't think EE policy allows me to help you with that.
0
 

Author Closing Comment

by:chima
Comment Utility
MacroShadow, thanks
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now