Page 1 of 2 12 LastLast
Results 1 to 10 of 17

highlighting a cell that contains a specific word within a phrase

This is a discussion on highlighting a cell that contains a specific word within a phrase within the Excel Questions forums, part of the Question Forums category; i can't get the conditional formating to work. what im trying to do is look through each cell in a ...

  1. #1
    New Member
    Join Date
    Mar 2012
    Posts
    21

    Default highlighting a cell that contains a specific word within a phrase

    i can't get the conditional formating to work. what im trying to do is look through each cell in a specific column and each cell that contains the word MSCI ( it will be one word in a phrase) highlight that row yellow. Does anyone know how to do this in VBA? Also i will need use this macro on numerous other spreedsheets is there a way to write it were it is available everytime excel is open and is not specific to this one spreadsheet?

    Thanks for your time

  2. #2
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    26,998

    Default Re: highlighting a cell that contains a specific word within a phrase

    This can be actually done pretty easily with Conditional Formatting.

    Use this formula for Excel 2007 and later (written for cell A1):
    Code:
    =IFERROR(FIND("MSCI",A1),0) > 0
    Use this formula for Excel 2003 and earlier (written for cell A1):
    Code:
    =IF(ISERROR(FIND("MSCI",A1)),0,1) > 0
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    Board Regular daniels012's Avatar
    Join Date
    Jan 2005
    Location
    34 56' 19" N / 82 13' 38" W
    Posts
    5,162

    Default Re: highlighting a cell that contains a specific word within a phrase

    Conditional formatting will do what you want.
    Go to Cell A3
    Use this in the formula part =search("MCSI",A3)
    That will highlight the cell if you want the row, just add the same conditional formatting in all the cells to the right.

    Michael
    "It is so nice, to see so many, that know so much"

    Using Excel 2007

  4. #4
    Board Regular daniels012's Avatar
    Join Date
    Jan 2005
    Location
    34 56' 19" N / 82 13' 38" W
    Posts
    5,162

    Default Re: highlighting a cell that contains a specific word within a phrase

    Forgot about catching the error!
    Thanks Joe4
    "It is so nice, to see so many, that know so much"

    Using Excel 2007

  5. #5
    Board Regular T. Valko's Avatar
    Join Date
    May 2009
    Location
    Pittsburgh
    Posts
    16,133

    Default Re: highlighting a cell that contains a specific word within a phrase

    Quote Originally Posted by daniels012 View Post
    Forgot about catching the error!
    Thanks Joe4
    No need to worry about any errors.

    If the formula returns an error that means it couldn't find MSCI and since it can't find MSCI the cell will not be formatted.

    When using formulas to define CF rules, if the formula evaluates to TRUE or ANY number other than 0 then the format will be applied. If the formula evaluates to any other value the format will not be applied.
    .
    Biff
    Microsoft MVP - Excel

    Using Excel 2002, 2007
    KISS - Keep It Simple Stupid

  6. #6
    New Member
    Join Date
    Mar 2012
    Posts
    21

    Default Re: highlighting a cell that contains a specific word within a phrase

    thanks instead of creating a condition for each cell can i use a range instead of cloumn a1 us a1:a1600?

  7. #7
    New Member
    Join Date
    Mar 2012
    Posts
    21

    Default Re: highlighting a cell that contains a specific word within a phrase

    Quote Originally Posted by Joe4 View Post
    This can be actually done pretty easily with Conditional Formatting.

    Use this formula for Excel 2007 and later (written for cell A1):
    Code:
    =IFERROR(FIND("MSCI",A1),0) > 0
    Use this formula for Excel 2003 and earlier (written for cell A1):
    Code:
    =IF(ISERROR(FIND("MSCI",A1)),0,1) > 0
    will this only work if MSCI is the only word in the cell? it will be part of a phrase how do i isolate that particular bit of text? as of now it will either turn every cell in the column yellow, or if i switch it not highlight anything at all


    are 500 cells each cell has an index in it S&P500 etc there are multiple MSCI indexes but with different endings MSCI followed by an industrial, midcap etc etc all the ones with MSCI i want highlighted yellow. do i have make a condition with each row that has the msci or can i get it were it will scan and highlight all of them with one piece of code/formula??

  8. #8
    Board Regular T. Valko's Avatar
    Join Date
    May 2009
    Location
    Pittsburgh
    Posts
    16,133

    Default Re: highlighting a cell that contains a specific word within a phrase

    Quote Originally Posted by jmac98 View Post
    will this only work if MSCI is the only word in the cell? it will be part of a phrase how do i isolate that particular bit of text? as of now it will either turn every cell in the column yellow, or if i switch it not highlight anything at all


    are 500 cells each cell has an index in it S&P500 etc there are multiple MSCI indexes but with different endings MSCI followed by an industrial, midcap etc etc all the ones with MSCI i want highlighted yellow. do i have make a condition with each row that has the msci or can i get it were it will scan and highlight all of them with one piece of code/formula??
    Tell us the exact range of cells you want to format. Also, tell us what version of Excel you're using.
    .
    Biff
    Microsoft MVP - Excel

    Using Excel 2002, 2007
    KISS - Keep It Simple Stupid

  9. #9
    New Member
    Join Date
    Mar 2012
    Posts
    21

    Default Re: highlighting a cell that contains a specific word within a phrase

    Quote Originally Posted by T. Valko View Post
    Tell us the exact range of cells you want to format. Also, tell us what version of Excel you're using.
    exact range E1:E1794

    im using excel 2003

  10. #10
    Board Regular T. Valko's Avatar
    Join Date
    May 2009
    Location
    Pittsburgh
    Posts
    16,133

    Default Re: highlighting a cell that contains a specific word within a phrase

    Quote Originally Posted by jmac98 View Post
    exact range E1:E1794

    im using excel 2003
    Try this...

    Select the *entire* range E1:E1794 starting from cell E1.
    Cell E1 will be the active cell. The active cell is the
    one cell in the selected range that is not shaded. The
    formula will be relative to the active cell.

    Goto the menu Format>Conditional Formatting
    Select the Formula Is option
    Enter this formula in the box on the right:

    =SEARCH("MCSI",E1)

    Click the Format button
    Select the Patterns tab
    Select a shade of yellow
    OK out
    .
    Biff
    Microsoft MVP - Excel

    Using Excel 2002, 2007
    KISS - Keep It Simple Stupid

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com