Closed Thread
Results 1 to 6 of 6

Highlight cell if it appears on another sheet?

  1. #1
    Registered User
    Join Date
    09-03-2010
    Location
    London, England
    MS-Off Ver
    Excel 2016
    Posts
    26

    Highlight cell if it appears on another sheet?

    Hi Guys, I hope you can help.

    I have a list of numbers in one sheet which I would like to highlight if they appear in a range of cells on another sheet?

    Someone came up with a great solution which was: =ISNUMBER(MATCH("*"&D5&"*",'Sheet 2'!$C$4:$C$6,0))

    The only issue is that when it's just a single number in the reference table e.g. "XX" instead of "XX, XX" it doesn't work? - See sheet attached - 75 is showing as FALSE?

    Any help would be greatly appreciated.

    Thanks, James
    Attached Files Attached Files

  2. #2
    Forum Expert Josť Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    2,602

    Re: Highlight cell if it appears on another sheet?

    Hi

    You can try this formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    09-03-2010
    Location
    London, England
    MS-Off Ver
    Excel 2016
    Posts
    26

    Re: Highlight cell if it appears on another sheet?

    Thanks Jose,

    That worked but it's making the file really heavy. 10-15 second delay anytime i change anything.

    Is there another solution or a workaround?

    Thanks, James

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    16,802

    Re: Highlight cell if it appears on another sheet?

    Not sure why you started a new thread, rather than continuing in your existing thread, but try this in F5 (or G5 in your latest file):

    =IF(ISNUMBER(MATCH("*"&D5&"*",'Sheet 2'!$C$4:$C$7,0)),TRUE,ISNUMBER(MATCH(D5,'Sheet 2'!$C$4:$C$7,0)))

    then copy down.

    Hope this helps.

    Pete
    Last edited by Pete_UK; 02-12-2019 at 02:30 PM.

  5. #5
    Forum Expert Josť Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    2,602

    Re: Highlight cell if it appears on another sheet?

    Hi James

    I suppose this formula is better, try

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2016 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    23,045

    Re: Highlight cell if it appears on another sheet?

    Do not open duplicate threads. Please continue in the one you started earlier.

    Go here: https://www.excelforum.com/excel-gen...her-sheet.html
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    STOP PRESS: Forum Rules Updated September 2018! Please read them here.
    If anyone has helped you towards solving a problem, then you can thank them by clicking on their reputation star bottom left.

Closed 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