Solved

MS Excel Search Find and Highlight row

Posted on 2014-04-07
8
2,289 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 19

Assisted Solution

by:helpfinder
helpfinder earned 50 total points
ID: 39984250
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 27

Expert Comment

by:MacroShadow
ID: 39984280
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
ID: 39990330
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
Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 

Author Comment

by:chima
ID: 39990354
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
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39990675
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
ID: 39994520
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 27

Accepted Solution

by:
MacroShadow earned 450 total points
ID: 39994553
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
ID: 40021152
MacroShadow, thanks
0

Featured Post

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

728 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