+ Reply to Thread
Results 1 to 7 of 7

Excel 2007 : Conditional formatting - highligh based on contents of multiple cells

  1. #1
    Registered User
    Join Date
    11-22-2011
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    2

    Conditional formatting - highligh based on contents of multiple cells

    Hello,

    I have an Excel sheet containing a list of items and their attributes. Each item can have up to eight unique attributes from a common pool of over 20 attributes. The item's name is in column A, attribute #1 in column B, attribute #2 in column C, etc. (Please see the attachment for a sample table in this format)

    I would like to use conditional formatting to highlight all item names (in column A) that have a specific attribute (let's say "R", for example). I know how to do this using a series of nested IF statements, but that is messy and difficult to modify when needed. Is there any simpler way of doing so?

    -Sklar
    Attached Files Attached Files
    Last edited by sklar; 11-22-2011 at 01:47 PM.

  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 - highligh based on contents of multiple cells

    Try conditional format formula after select A2:A7:


    =COUNTIF($B2:$F2,"R")
    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
    11-22-2011
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Conditional formatting - highligh based on contents of multiple cells

    Perfect! Thank you so much!

  4. #4
    Registered User
    Join Date
    05-13-2011
    Location
    Birmingham
    MS-Off Ver
    Excel 2007
    Posts
    78

    Re: Conditional formatting - highligh based on contents of multiple cells

    sklar!
    After selecting A2:A7 and applying formula, it highlights all the cells irrespective of R. How you do that?

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

    Re: Conditional formatting - highligh based on contents of multiple cells

    nlm, I am not sure what your comment is about. Did you test it, properly? It should only highlight A2, A4 and A6 on sklar's workbook, because they each have an "R" in their respective rows.

  6. #6
    Registered User
    Join Date
    05-13-2011
    Location
    Birmingham
    MS-Off Ver
    Excel 2007
    Posts
    78

    Re: Conditional formatting - highligh based on contents of multiple cells

    NBVC!
    Your formula works after removing $ sign...

    Thanks.

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

    Re: Conditional formatting - highligh based on contents of multiple cells

    If you are selecting only A2:A7, then it works with or without the $ signs.

+ 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