+ Reply to Thread
Results 1 to 3 of 3

Highlighting matching cells between many columns within a row

  1. #1
    Registered User
    Join Date
    06-27-2012
    Location
    La Porte, TX
    MS-Off Ver
    Excel 2007
    Posts
    2

    Highlighting matching cells between many columns within a row

    Excel Example.JPG

    If the above image did not render, here is a link to the image of the excel file I refer to in my question.

    http://i.stack.imgur.com/QYyyY.png

    Basically, I want to Excel to look through a row in the green area, and search for any matches within the same row in the pink area. For example, in row 1, cells C1 and E1 would be highlighted, as their values (3 and 5, respectively) match values found in the pink area in the same row.

    If this is not possible, it would be helpful if I could just make an 'if' statement that reported "Match" if any of the cells in the green area matched any of the cells in the pink area within the same row, or otherwise report "No Match". For example, row 1 would report "Match" as two of the values are identical between the green and pink. Row 5, however, would report "No Match" as none of the green values match any of the pink values within row 5. I tried using =if(or(A1,B1,C1,D1,E1,F1)=or(H1,I1,J1...),"Match", "No Match", but that seemed to always result in a "Match" for any pink row that had any numerical cell value at all...

    Any help would be greatly appreciated!!

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Highlighting matching cells between many columns within a row

    Okay so select your green range H1:O20

    Conditional Formatting>New Rule>Use Formula

    =ISNUMBER(MATCH(H1,$A1:$E1,0))
    Format appropriately.
    Does that work for you?
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    06-27-2012
    Location
    La Porte, TX
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Highlighting matching cells between many columns within a row

    Quote Originally Posted by ChemistB View Post
    Okay so select your green range H1:O20

    Conditional Formatting>New Rule>Use Formula

    =ISNUMBER(MATCH(H1,$A1:$E1,0))
    Format appropriately.
    Does that work for you?


    Thanks ChemistB!! I was unfamiliar with both the isnumber function and match, but using them in concert worked very well. The formula you gave didn't work for me (highlighting the wrong cells, likely something I did wrong), so I changed it around to this:

    =ISNUMBER(MATCH(A1,$H1:$O1,0))

    Which worked. Your idea was an easy and creative solution to my problem, thank you again!
    Last edited by jackfjo3; 06-27-2012 at 05:20 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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