+ Reply to Thread
Results 1 to 9 of 9

Is it possible to have two search functions in one sheet with Conditional Formatting?

  1. #1
    Registered User
    Join Date
    09-29-2016
    Location
    Stockholm, Sweden
    MS-Off Ver
    Office 365
    Posts
    5

    Is it possible to have two search functions in one sheet with Conditional Formatting?

    Hi everybody,

    I'm trying to build a simple search (highlight) function with Conditional Formatting. If I write in
    text in a "search-cell" I want all other cells with the same text in that sheet to get coloured.
    So far it's quite basic and straightforward.

    In the Conditional Formatting Rules Manager I've got:
    Formula: =ISBLANK($D$1) with "No Format Set" to clear the format if the search cell is empty.
    Cell Value = $D$1 with the format set to fill the target cells with colour if they match the search cell.

    This works like a charm.

    The problem is that I want two "Search cells" to make it possible to search for two different text strings at the same time. But if I simply make two sets of the above rules they interfere with each other, ie if search cell 2 is empty the search cell 1 does not highlight the cells any more.
    I've been fiddling around with "stop if true" and tried rewrite the formatting rules, but to no avail.

    Can anyone point me in the right direction here, is there any way to have two search/highlight

    functions in one sheet? It doesn't matter if it's in VBA or CF (I'm a newbie to VBA, so I haven't

    tried it with this problem).

    In the attached file, type something in Search2 (G1) you'll see what I mean.
    Attached Files Attached Files
    Last edited by RosQ; 09-29-2016 at 05:48 PM.

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Is it possible to have two search functions in one sheet with Conditional Formatting?

    Hello & Welcome to the Forum,

    Maybe I don't quite understand, but what you have will work independently of each other as long as you uncheck the Stop if True.

    Now if you only want to use Search2, only cell that match Search2 will highlight.

    Not sure why you need the =ISBLANK($G$1) set to No Format
    Last edited by jeffreybrown; 09-29-2016 at 05:56 PM.
    HTH
    Regards, Jeff

  3. #3
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Is it possible to have two search functions in one sheet with Conditional Formatting?

    I think this will do what you want
    Attached Files Attached Files
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  4. #4
    Registered User
    Join Date
    09-29-2016
    Location
    Stockholm, Sweden
    MS-Off Ver
    Office 365
    Posts
    5

    Re: Is it possible to have two search functions in one sheet with Conditional Formatting?

    Thanks for your extremely quick responses!

    But I still don't get it to work. The scenario is that a lot of users will use a scheme to find their names in it, I made a simple version of the scheme in the attached file. But when I make (random) changes to the search cells and the other cells it all collapses. If a cell is empty I don't want it to be coloured by the "search". That's why I tried with the "No Format Set". But after a few searches I start to get the empty cells coloured anyway. And sometimes when I empty one of the search cells the colours from the other one is lost as well. To see this clearly you need to change the values (randomly) in both the search cells and the other cells.
    The content of the "other" cells will change quit a lot when the workbook is in use.

    When I use only one set of the rules it all works perfectly, but with two sets it starts to get messy.

  5. #5
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Is it possible to have two search functions in one sheet with Conditional Formatting?

    Sorry, but I'm not seeing the problem...

    With the attached, what is not working as you desire?

    If I put something in Search1, all those cells that match highlight. If I put something in Search2, the same.

    I can search with just one cell (D1 or G1) or both. What am I missing?
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    09-29-2016
    Location
    Stockholm, Sweden
    MS-Off Ver
    Office 365
    Posts
    5

    Re: Is it possible to have two search functions in one sheet with Conditional Formatting?

    Thanks for your effort!

    I'm sorry, but I find it hard to explain. In the attached picture 1 I have marked empty cells that I don't want to get coloured even if the search cell is empty.

    In the second picture I try to show what happens if I try to solve the problem with empty cells, I've checked the "Stop if True" but then the search don't work at all.

    This is just a couple of examples, I've tried a lot of "versions" with "Stop if True" checked or unchecked, different order of the rules, and so on. But I haven't been able to combine the two search functions with with the criteria that no empty cell should be coloured.

    I'll hope this explain in a better way.
    Attached Images Attached Images

  7. #7
    Valued Forum Contributor
    Join Date
    06-21-2013
    Location
    Bangladesh
    MS-Off Ver
    Excel 2000, Excel XP, Excel 2002, Excel 2007, Excel 2010, Excel 2013
    Posts
    975

    Re: Is it possible to have two search functions in one sheet with Conditional Formatting?

    You are using wrong CF rules. To achieve your result use formula in CF. You need only 2 rules there.
    One :
    Please Login or Register  to view this content.
    Two :
    Please Login or Register  to view this content.
    See the attachment.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    09-29-2016
    Location
    Stockholm, Sweden
    MS-Off Ver
    Office 365
    Posts
    5

    Talking Re: Is it possible to have two search functions in one sheet with Conditional Formatting?

    PERFECT! This is exactly what I needed, I just couldn't figure it out by myself. It both solved my problem and simplified the CF rules.

    Thank you!!!

  9. #9
    Valued Forum Contributor
    Join Date
    06-21-2013
    Location
    Bangladesh
    MS-Off Ver
    Excel 2000, Excel XP, Excel 2002, Excel 2007, Excel 2010, Excel 2013
    Posts
    975

    Re: Is it possible to have two search functions in one sheet with Conditional Formatting?

    You are welcome.

+ 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. Conditional Formatting or Functions
    By Shabir in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-24-2014, 01:33 PM
  2. If functions and Conditional formatting
    By stpeter in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-19-2013, 11:03 AM
  3. Conditional Formatting/Functions
    By JessicaB517 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-21-2012, 02:22 PM
  4. IF Functions, Conditional Formatting
    By argegg26 in forum Excel General
    Replies: 3
    Last Post: 03-30-2010, 09:17 AM
  5. conditional formatting using IF with MID functions
    By jdubwelch in forum Excel General
    Replies: 6
    Last Post: 11-20-2007, 12:07 PM
  6. conditional formatting using IF with MID functions
    By jdubwelch in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-20-2007, 01:46 AM
  7. Conditional Formatting- Functions
    By jbroad in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-17-2007, 09:24 PM

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