Your browser isn't supported
It looks like you're using an old web browser. To get the most out of the site and to ensure guides display correctly, we suggest upgrading your browser now. Download the latest:

Welcome to the MSE Forums

We're home to a fantastic community of MoneySavers but anyone can post. Please exercise caution & report spam, illegal, offensive or libellous posts/messages: click "report" or email forumteam@. Skimlinks & other affiliated links are turned on

Search
  • FIRST POST
    • Verb
    • By Verb 7th Jul 17, 9:28 AM
    • 162Posts
    • 5Thanks
    Verb
    Excel
    • #1
    • 7th Jul 17, 9:28 AM
    Excel 7th Jul 17 at 9:28 AM
    Hi
    Can someone offer some assistance. I'll try and explain best I can.


    I'm trying to add a function to my sheet where I put a value in a specific cell and sheet searches the rest of the sheet and highlights the two in question. I've used conditional formatting but it highlighting all duplicates.


    I would like to input a value in cell A1 and on the sheet it highlights A1 and any values that match A1 only


    Thanks
Page 1
    • Le_Kirk
    • By Le_Kirk 7th Jul 17, 9:50 AM
    • 1,984 Posts
    • 1,001 Thanks
    Le_Kirk
    • #2
    • 7th Jul 17, 9:50 AM
    • #2
    • 7th Jul 17, 9:50 AM
    Apart from writing a Macro (visual basic) you could use Find & Replace but it is a very manual action.
    • Andy L
    • By Andy L 7th Jul 17, 10:03 AM
    • 8,402 Posts
    • 6,697 Thanks
    Andy L
    • #3
    • 7th Jul 17, 10:03 AM
    • #3
    • 7th Jul 17, 10:03 AM
    You could use conditional formatting rather than a function
    • DoaM
    • By DoaM 7th Jul 17, 10:11 AM
    • 2,989 Posts
    • 3,017 Thanks
    DoaM
    • #4
    • 7th Jul 17, 10:11 AM
    • #4
    • 7th Jul 17, 10:11 AM
    I've used conditional formatting but it highlighting all duplicates
    Originally posted by Verb
    What's the difference between a duplicate and a matching value?
    Diary of a madman
    Walk the line again today
    Entries of confusion
    Dear diary, I'm here to stay
    • ThemeOne
    • By ThemeOne 7th Jul 17, 10:19 AM
    • 1,020 Posts
    • 855 Thanks
    ThemeOne
    • #5
    • 7th Jul 17, 10:19 AM
    • #5
    • 7th Jul 17, 10:19 AM
    As I understand it, if you type 25 into cell A1 you would like this cell plus any other cells on the spreadsheet which also equal 25 to be highlighted.

    Surely that is the same as highlighting duplicates?

    If this is what you need, then conditional formatting is the way to go.
    • Verb
    • By Verb 7th Jul 17, 1:59 PM
    • 162 Posts
    • 5 Thanks
    Verb
    • #6
    • 7th Jul 17, 1:59 PM
    • #6
    • 7th Jul 17, 1:59 PM
    As I understand it, if you type 25 into cell A1 you would like this cell plus any other cells on the spreadsheet which also equal 25 to be highlighted.

    Surely that is the same as highlighting duplicates?
    Originally posted by ThemeOne

    That's what I originally though. But in actual fact anything with a duplicate value is highlighted which means the value I put in A1 is mixed in with the other highlighted values. I would like to just have A1 and any values that match only that
    • Verb
    • By Verb 7th Jul 17, 2:01 PM
    • 162 Posts
    • 5 Thanks
    Verb
    • #7
    • 7th Jul 17, 2:01 PM
    • #7
    • 7th Jul 17, 2:01 PM
    What's the difference between a duplicate and a matching value?
    Originally posted by DoaM

    There could be loads of duplicates and only 1 matching value
    • DoaM
    • By DoaM 7th Jul 17, 2:45 PM
    • 2,989 Posts
    • 3,017 Thanks
    DoaM
    • #8
    • 7th Jul 17, 2:45 PM
    • #8
    • 7th Jul 17, 2:45 PM
    There could be loads of duplicates and only 1 matching value
    Originally posted by Verb
    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).
    Diary of a madman
    Walk the line again today
    Entries of confusion
    Dear diary, I'm here to stay
    • rmg1
    • By rmg1 7th Jul 17, 3:22 PM
    • 2,868 Posts
    • 731 Thanks
    rmg1
    • #9
    • 7th Jul 17, 3:22 PM
    • #9
    • 7th Jul 17, 3:22 PM
    If you don't want A1 to be highlighted, then just exclude it from the conditional formatting.
    Or am I also misreading the question?
    Flagellation, necrophilia and bestiality - Am I flogging a dead horse?

    Any posts are my opinion and only that. Please read at your own risk.
    • Cornucopia
    • By Cornucopia 7th Jul 17, 3:29 PM
    • 9,038 Posts
    • 8,474 Thanks
    Cornucopia
    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).
    Originally posted by DoaM

    Maybe the OP is actually wanting to test 2 values, hence "two" in the original question?

    If so, it'll probably need a formula within the data to combine the values into a single, searchable column and then apply the lookups, conditional formatting, etc. to it.

    Or perhaps, they want to highlight only the first (2?) matching value in the column?

    Basic Conditional formatting certainly works for me (I needed to put the reference cell as fixed location e.g. =$B$4). Once the rule is defined and working for one cell, use the Format Painter tool to apply it to all the others.

    I see that my version of Excel Starter has loads of other goodies under the Conditional Formatting menu. I shall have to explore.


    EDIT: Reading it again, I wonder if it is this: Highlight any cells in the data that match A1, AND highlight A1 if any cells match?
    Last edited by Cornucopia; 07-07-2017 at 3:43 PM.
    I'm a Board Guide on the Phones & TV, Techie Stuff, In My Home,
    The Money Savers Arms and Food Shopping boards. I'm a volunteer to help the boards run smoothly, and I can move and merge threads there. Any views are mine and not the official line of moneysavingexpert.com.

    Board guides are not moderators. If you spot an inappropriate or illegal post then please report it to forumteam@moneysavingexpert.com
    • Chino
    • By Chino 7th Jul 17, 3:44 PM
    • 377 Posts
    • 173 Thanks
    Chino
    what makes a matching cell a duplicate and what makes a matching cell a matching value? How are the two things different?
    Originally posted by DoaM
    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.
    • Cornucopia
    • By Cornucopia 7th Jul 17, 3:54 PM
    • 9,038 Posts
    • 8,474 Thanks
    Cornucopia
    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)))
    I'm a Board Guide on the Phones & TV, Techie Stuff, In My Home,
    The Money Savers Arms and Food Shopping boards. I'm a volunteer to help the boards run smoothly, and I can move and merge threads there. Any views are mine and not the official line of moneysavingexpert.com.

    Board guides are not moderators. If you spot an inappropriate or illegal post then please report it to forumteam@moneysavingexpert.com
    • Andy L
    • By Andy L 7th Jul 17, 4:40 PM
    • 8,402 Posts
    • 6,697 Thanks
    Andy L
    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.
    Originally posted by Chino
    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
    Last edited by Andy L; 07-07-2017 at 4:45 PM.
    • Andy L
    • By Andy L 7th Jul 17, 4:44 PM
    • 8,402 Posts
    • 6,697 Thanks
    Andy L
    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).
    Originally posted by DoaM
    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
    • By Hedgehog99 7th Jul 17, 4:44 PM
    • 1,285 Posts
    • 2,666 Thanks
    Hedgehog99
    Can you use an IF function?
    • ThemeOne
    • By ThemeOne 7th Jul 17, 5:37 PM
    • 1,020 Posts
    • 855 Thanks
    ThemeOne
    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.
Welcome to our new Forum!

Our aim is to save you money quickly and easily. We hope you like it!

Forum Team Contact us

Live Stats

234Posts Today

1,206Users online

Martin's Twitter