+ Reply to Thread
Results 1 to 8 of 8

Easy search & highlight function in Excel doesn't work...

  1. #1
    Registered User
    Join Date
    08-16-2012
    Location
    Almere
    MS-Off Ver
    Excel 2003
    Posts
    48

    Easy search & highlight function in Excel doesn't work...

    I've seen many videos now on YouTube and other sites explaing how to use the search function in Excel and it just doesn't work with my excel....
    I have three coloms in my sheet (A, B & C)
    They all have 10 rows with data (A1-10, B1-10, C1-10)
    What I want is to type some text in Cell E1 and the typed text will then be searched for in the range A1:C10
    If the text is found, the cell is highlighted with a different color.

    Apparently this should be very easy to do if I believe all the videos and sites but not with me :-(

    Who can help me solve this ???? Don't want to do it with VBA as it should be a build in option with excel....

  2. #2
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016 / 365
    Posts
    3,440

    Re: Easy search & highlight function in Excel doesn't work...

    Hi,

    Highlight A1:C10 with A1 selected

    Conditional formatting > add a rule > use a formula to determine

    Enter the formula =A1=$E$1

    Select your preferred formatting.
    Rule 1: Never merge cells
    Rule 2: See rule 1

    "Tomorrow I'm going to be famous. All I need is a tennis racket and a hat".

  3. #3
    Registered User
    Join Date
    08-16-2012
    Location
    Almere
    MS-Off Ver
    Excel 2003
    Posts
    48

    Re: Easy search & highlight function in Excel doesn't work...

    Did exactly what you wrote but again it's not working.....
    If Cell E1 is empty, all the cells A1:C10 are colored green (as set with preferred formatting)
    When I type some text that is visible in the range A1:C10 nothing happens

  4. #4
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    3,256

    Re: Easy search & highlight function in Excel doesn't work...

    Please upload your excel file
    Then we can see why it is not working.
    Willem
    English is not my native language sorry for errors
    Please correct me if I'm completely wrong

  5. #5
    Registered User
    Join Date
    08-16-2012
    Location
    Almere
    MS-Off Ver
    Excel 2003
    Posts
    48

    Re: Easy search & highlight function in Excel doesn't work...

    Found the error / problem after testing it again but now on a new sheet
    On the new sheet it works but I tested it with single words !!
    Back to my actual sheet and it's not working again.
    Problem is that the search string is not exactly the same as the value in the cell
    The cells all contain a first and lastname. When I type Ralph there's no result. When I typ Ralph van Dyk it works !!
    Is it possible to change the formula so that is also highlights a cell when there's a part of the text found ??
    Last edited by minifreak; 10-05-2017 at 09:30 AM.

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Easy search & highlight function in Excel doesn't work...

    Try

    A1="Ralph Van Dyke"

    =ISNUMBER(SEARCH("van D",$A$1))

    will return TRUE if match found

    change the "Van D" to whatever is required e.g. Ralph, Ral .....

  7. #7
    Registered User
    Join Date
    08-16-2012
    Location
    Almere
    MS-Off Ver
    Excel 2003
    Posts
    48

    Re: Easy search & highlight function in Excel doesn't work...

    Not exactly what I need or it's not working again..
    This part was working correctly =A1=$E$1
    If I type Ralph Van Dyke in E1 the cell where this value is will be highlighted with a green color.
    As soon as I typ Van Dyke of just Ralph, nothing happens.
    So I tried your last formula =ISNUMBER(SEARCH("van D",$A$1)) and changed it to =ISNUMBER(SEARCH($E$1,$A$1)) because it needs to find the value (or part of) what's typed in E1
    but this is not working....

  8. #8
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Easy search & highlight function in Excel doesn't work...

    Again ...
    Quote Originally Posted by popipipo View Post
    Please upload your excel file
    Then we can see why it is not working.
    If you are not familiar with how to do this:

    To attach a file to your post,
    • be sure to desensitize the data
    • click “Go Advanced” (next to Post Quick Reply – bottom right),
    • scroll down until you see “Manage Attachments”, click that,
    • click “Browse”.
    • select your file(s)
    • click “Upload”
    • click “Close window”
    • click “Submit Reply”

    The file name will appear at the bottom of your reply.
    Dave

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 09-27-2017, 10:42 AM
  2. Easy way to search a sheet and highlight?
    By Skiny in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 09-28-2013, 09:53 AM
  3. Excel drag function doesn't work properly
    By aserf in forum Excel General
    Replies: 2
    Last Post: 05-14-2010, 04:39 AM
  4. Chart.Labels.Add function doesn't work in excel 2007
    By narek1 in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 03-11-2010, 06:40 AM
  5. Excel Trim , Clean or IsNonText function doesn't work
    By munzer1 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-28-2007, 06:25 PM
  6. [SOLVED] Paste function in Excel doesn't work
    By Bob Reynolds in forum Excel General
    Replies: 3
    Last Post: 01-16-2006, 07:55 AM
  7. Replies: 1
    Last Post: 08-03-2005, 09:05 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1