Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

MS Excel Search Find and Highlight row

Posted on 2014-04-07
8
Medium Priority
?
2,510 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 200 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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 1800 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

636 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