+ Reply to Thread
Results 1 to 9 of 9

Dynamic Search

  1. #1
    Registered User
    Join Date
    10-31-2011
    Location
    Singapore
    MS-Off Ver
    Microsoft 365 (Windows 10)
    Posts
    62

    Dynamic Search

    Hi, i'm trying to do a Dynamic Search and its a little out of my league!

    When i type a keyword in the ActiveX text Box, i would like it to highlight every row in C5:G7 using conditional formatting that contains the search term in range J5:T7.

    I'm not sure what i've got wrong in the formula in the 'data' worksheet

    For example:

    If i type 'Nick', row C6:G6 is highlighted amber
    If i type 'Alibaba', rows C5:G6 and C7:G7 are highlighted Amber
    Attached Files Attached Files

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: Dynamic Search

    Check out the attached.
    Attached Files Attached Files

  3. #3
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    Re: Dynamic Search

    Hi!!
    I have made the changes in your worksheet
    See if this is what you want
    This works for 100 rows change the array in conditional formatting if you want more rows.
    Regards
    Attached Files Attached Files

  4. #4
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    Re: Dynamic Search

    [QUOTE=sourabhg98;3946246]Hi!!
    I have made the changes in your worksheet
    See if this is what you want
    This works for 100 rows change the array in conditional formatting if you want more rows.
    Regards
    [It was my pleasure to help you. If you are satisfied with my answer then please Click on Add Reputation. Thanks a lot . /QUOTE]

  5. #5
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    Re: Dynamic Search

    [QUOTE=sourabhg98;3946246]Hi!!
    I have made the changes in your worksheet
    See if this is what you want
    This works for 100 rows change the array in conditional formatting if you want more rows.
    Regards

    [/It was my pleasure to help you. If you are satisfied with my answer then please Click on Add Reputation. Thanks a lot . ]

  6. #6
    Registered User
    Join Date
    10-31-2011
    Location
    Singapore
    MS-Off Ver
    Microsoft 365 (Windows 10)
    Posts
    62

    Re: Dynamic Search

    Thanks Davesexcel. (sourabhg98, your file didnt download.)

    Dave, the search bar is pulling up instances everywhere on the wroksheet the search term is located and highligting the rown its found in...

    just clarify, I don't require the 'tags' to be highlight in a search, i only need the rows in the range c5:g7 to be hihlighted. (going off the tags in their respective rows. Hope that makes sense...

    I was using this video as a guide, but i just cant get the formula to work! This person doesn't use VBA, just a simple formula and conditional formatting.

    Thanks, Appreciate any help you may offer on this. i'm stumped.

    MM

  7. #7
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    Re: Dynamic Search

    Hello I have re-uploaded the file....check if it downloads now....
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    10-31-2011
    Location
    Singapore
    MS-Off Ver
    Microsoft 365 (Windows 10)
    Posts
    62

    Re: Dynamic Search

    afraid not.... says network error. everything else seems fine on my end...

  9. #9
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    Re: Dynamic Search

    can you please give me your mail id i will send you there...

    Or follow these steps---
    1. Select the range B5:G100 (You can change this 100 to whatever no. of rows you want, but dont change 5)
    2. Go to home tab>>> Conditional Formatting>>new rule......in rule type select >>use a formula to determine which cells to format
    3. in the formula bar paste this formula
    =IF($E$2="","",IF(IFERROR(SEARCH($E$2,$J5&$K5&$L5&$M5&$N5&$O5&$P5&$Q5&$R5&$S5&$T5),"f")="f","",1))=1
    4. Click on format button and select the format you want i.e. amber color and outline border
    5. Click Ok>>Apply>>Ok

    This will work for sure .....I have tested

    If it works then please click on add reputation below .....
    Regards
    Last edited by sourabhg98; 01-06-2015 at 05:23 AM.

+ 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. Dynamic search function via VBA
    By Jovica in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-18-2013, 10:58 AM
  2. Dynamic search to add data
    By spidykwya in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-06-2012, 11:23 PM
  3. Search Dynamic words within Column
    By bayatmu in forum Excel General
    Replies: 3
    Last Post: 03-20-2012, 04:52 PM
  4. Dynamic Search?!
    By memosky in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-05-2007, 12:08 AM
  5. dynamic search
    By Lori in forum Excel General
    Replies: 0
    Last Post: 02-04-2006, 07:45 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