+ Reply to Thread
Results 1 to 4 of 4

conditional formatting if any of multiple text in cell itself

  1. #1
    Registered User
    Join Date
    09-18-2009
    Location
    Pennsylvania
    MS-Off Ver
    Excel 2003
    Posts
    4

    conditional formatting if any of multiple text in cell itself

    For multiple cells in a fairly large spreadsheet, I would like to use conditional formatting (e.g. apply a red highlighted background) if the cell itself is equal to any of about 20 potential words (examples of these words are "unknown" or "N/A" or "not computed" or "score not calculated" or "Not reported", etc.). I would want the conditional statement to say something like, "If cell is equal to = "unknown" or "N/A" or "not computed" or "not reported" then turn cell red.

    This is a data entry page that my data entry staff will be entering data into based on preset dropdown lists that I've already set. I just want to the file to automatically highlight in red the cells in which my data entry staff entered the specific words listed above, so that I can quickly scan the document and discuss the "red" cells with my data entry staff.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: conditional formatting if any of multiple text in cell itself

    If you have 20 odd words to look for, then I suggest you enter these 20 words somewhere in the sheet (and perhaps name that range something like Words)

    Then use Conditional Formatting formula:

    =Match(A1,Words,0)

    where A1 is the top most cell in your selected range...

    If you don't want to name the range you can refer to it directly as long as it is on same sheet.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    09-18-2009
    Location
    Pennsylvania
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: conditional formatting if any of multiple text in cell itself

    Thanks for your help! This is very close to solving my problem.

    To continue your example. If I named a list of 20 words "words" then I want cell C45 to turn red if the value in C45 is somewhere in "words" list then I would give C45 the conditional format of =MATCH(C45,words,0) and then it works perfectly for C45.

    But I would like to apply this formula to about 120 other noncontiguous cells across multiple sheets (e.g. LL3, RQ24, B34, A2, F34, D200, etc....on to naming about 120 other random cells). So that, for example, LL3 turns red if =MATCH(LL3,words,0), etc.

    How could I apply this formula to all these extra cells all one time by CTRL+selecting all 120 cells and then writing in the formula one time. Instead of having to individually write a separate code 120 times for 120 cells.

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: conditional formatting if any of multiple text in cell itself

    Copy the cell you entered a valid conditional format for the respective, cell then select other cells holding CTRL key down, then go to Edit|Paste Special and select Formats.

    The cell references should align appropriately.

    Also, the Named range only has to appear once in the workbook.

    But in each sheet, start by creating a conditional format on one cell and copy as per above to the other cells in the same sheet.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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