We'd like to remind Forumites to please avoid political debate on the Forum... Read More »
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
Comments
-
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)))0 -
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 important0 -
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.0 -
Can you use an IF function?0
-
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.0
This discussion has been closed.
Confirm your email address to Create Threads and Reply

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