We're aware that some users are experiencing technical issues which the team are working to resolve. See the Community Noticeboard for more info. Thank you for your patience.
📨 Have you signed up to the Forum's new Email Digest yet? Get a selection of trending threads sent straight to your inbox daily, weekly or monthly!

Excel

Options
2»

Comments

  • Cornucopia
    Cornucopia Posts: 16,471 Forumite
    Part of the Furniture 10,000 Posts Name Dropper Photogenic
    It's unclear at the moment what the OP means by "duplicate" and "matching".

    Anyway, with a bit of tinkering, I have the following formula to highlight cell A1 when it matches any of the values in column B. This formula needs to go into the conditional formatting field when using the option "use a formula to decide which cells to format".
    =NOT(ISERROR(MATCH(A1,B:B,0)))
  • Andy_L
    Andy_L Posts: 13,018 Forumite
    Part of the Furniture 10,000 Posts Name Dropper
    edited 7 July 2017 at 4:45PM
    Chino wrote: »
    The conditional formatting option provided by Excel to identify duplicates highlights any cell that has the same value as another cell.
    The OP only wants to highlight cells that have the same value as cell A1.

    You don't use the "Duplicate Values" option, you need to use the "Equal To" option which asks you for either a value or a cell reference

    Edit - this is in Excel 2007 if its important
  • Andy_L
    Andy_L Posts: 13,018 Forumite
    Part of the Furniture 10,000 Posts Name Dropper
    DoaM wrote: »
    Eh?

    I'll rephrase ... what makes a matching cell a duplicate and what makes a matching cell a matching value? How are the two things different?

    (Yes - I've read post #7 ... I'm still none-the-wiser).

    You could have 'a' in 3 different cells, 'b' in 4 cells, 'c' in one cell & 'd' in one cell & 'x' in A1 as your matching criteria

    Matching duplicates highlights all the 'a's & 'b's as they are duplicates of something else in the sheet.
  • Hedgehog99
    Hedgehog99 Posts: 1,425 Forumite
    Can you use an IF function?
  • ThemeOne
    ThemeOne Posts: 1,473 Forumite
    Part of the Furniture 1,000 Posts Combo Breaker
    Let's say the value in cell A1 is 25, and that is the value you want highlighted throughout the worksheet.

    Go to Conditional Formatting / New Rule - select "use a formula ..."

    In the "format values where this formula is true" box type =A1=25.

    Click the Format button and choose whatever highlight colour you'd like, then click OK.

    Now go to Conditional Formatting / Manage Rules - delete what is in the "Applies to" box then click the button on the spreadsheet to the left of column A and above row 1 - this will apply the conditional formatting to the whole worksheet. Click Apply and hopefully your highlights will appear.
This discussion has been closed.
Meet your Ambassadors

🚀 Getting Started

Hi new member!

Our Getting Started Guide will help you get the most out of the Forum

Categories

  • All Categories
  • 350.9K Banking & Borrowing
  • 253.1K Reduce Debt & Boost Income
  • 453.5K Spending & Discounts
  • 243.9K Work, Benefits & Business
  • 598.7K Mortgages, Homes & Bills
  • 176.9K Life & Family
  • 257.2K Travel & Transport
  • 1.5M Hobbies & Leisure
  • 16.1K Discuss & Feedback
  • 37.6K Read-Only Boards

Is this how you want to be seen?

We see you are using a default avatar. It takes only a few seconds to pick a picture.