MS Excel Search Find and Highlight row

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?
chimaAsked:
Who is Participating?
 
MacroShadowConnect With a Mentor Commented:
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
 
helpfinderConnect With a Mentor IT ConsultantCommented:
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
 
MacroShadowCommented:
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
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
chimaAuthor Commented:
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
 
chimaAuthor Commented:
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
 
MacroShadowCommented:
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
 
chimaAuthor Commented:
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
 
chimaAuthor Commented:
MacroShadow, thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.