+ Reply to Thread
Results 1 to 9 of 9

How to highlight a cell depending on duplicates elsewhere?

  1. #1
    Registered User
    Join Date
    04-27-2010
    Location
    Amstel
    MS-Off Ver
    Excel 2021
    Posts
    20

    How to highlight a cell depending on duplicates elsewhere?

    Hi there,

    I am trying to get a specific cell highlighted (conditional formatting) depending on if a range of cells elsewhere contains duplicates.
    So e.g. A1 gets highlighted because there are duplicates in A3:A9:

    2022-07-24_Gxb6.png

    (I tried to attach this simple excel sheet as an attachment, but the attachment button seems broken)

    edit
    Managed to attach the sheet, thnx Pepe Le Mokko
    Last edited by jistme; 07-24-2022 at 05:29 AM.

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: How to highlight a cell depending on duplicates elsewhere?

    Hi
    to attach a sheet see the yellow banner. The attachment button has been U/S for some time

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: How to highlight a cell depending on duplicates elsewhere?

    Select A3:A9 and use Conditional Formatting with the formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  4. #4
    Registered User
    Join Date
    04-27-2010
    Location
    Amstel
    MS-Off Ver
    Excel 2021
    Posts
    20

    Re: How to highlight a cell depending on duplicates elsewhere?

    I had tried something like that, but it doesn't work:

    2022-07-24_8RCu.png

    edit,
    I tried again, now dragging the formatting condition over the cells below, and now there is highlighting.
    But the highlighting is not in A1, but moves to lower cells depending on where the duplicates are.

    Could it have to do with the $ sign?
    (that's something that often confuses me)
    Last edited by jistme; 07-24-2022 at 05:59 AM.

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: How to highlight a cell depending on duplicates elsewhere?

    Apologies. I misunderstood the requirement. Select cell A1 and use the CF formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: How to highlight a cell depending on duplicates elsewhere?

    This might be a more generic solution:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  7. #7
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: How to highlight a cell depending on duplicates elsewhere?

    Select range A1 (or as required). Formula for CF

    =SUM(1*(COUNTIF($A$3:$A$9,$A$3:$A$9)>1))>0
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  8. #8
    Registered User
    Join Date
    04-27-2010
    Location
    Amstel
    MS-Off Ver
    Excel 2021
    Posts
    20

    Re: How to highlight a cell depending on duplicates elsewhere?

    Quote Originally Posted by TMS View Post
    This might be a more generic solution:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Thanks TMS! That is indeed doing as I was asking for.

    One thing I noticed though is that it does not seem to work if you have letters (or words) instead of numbers in the range?

    The solution provided by kvsrinivasamurthy also works great, and that one also seems to work with letters and words.
    So I'm going to use that one.

    Thank you both!

  9. #9
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: How to highlight a cell depending on duplicates elsewhere?

    With alpha-numeric content, use:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    But you're better off with kvsrinivasamurthy's solution

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Highlight duplicates - two sheets - based on two cell values
    By Tsinos in forum Excel General
    Replies: 7
    Last Post: 06-10-2022, 01:49 AM
  2. Highlight duplicates in 2 columns: Keep duplicates, delete rest
    By tobyvb in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-23-2021, 06:27 AM
  3. [SOLVED] highlight duplicates in one or more columns, only if the cell has a value inside (visible)
    By Alexander2020 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-10-2020, 10:37 AM
  4. Vba to highlight the cell and remove duplicates
    By ashup in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-05-2017, 05:21 AM
  5. Cell to highlight different colors depending on the date in another cell?
    By Gregwaa in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-14-2013, 10:42 AM
  6. Replies: 8
    Last Post: 02-27-2011, 06:46 AM
  7. Macro To Highlight Range of a row a Certain Color Depending On Contents In a Cell
    By donnydorko in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 09-18-2010, 02:23 PM

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