Excel

Verb

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

Le_Kirk

Apart from writing a Macro (visual basic) you could use Find & Replace but it is a very manual action.

Andy_L

You could use conditional formatting rather than a function

DoaM

Verb wrote: »

I've used conditional formatting but it highlighting all duplicates

What's the difference between a duplicate and a matching value?

ThemeOne

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

ThemeOne wrote: »

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?

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

DoaM wrote: »

What's the difference between a duplicate and a matching value?

There could be loads of duplicates and only 1 matching value

DoaM

Verb wrote: »

There could be loads of duplicates and only 1 matching value

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).

rmg1

If you don't want A1 to be highlighted, then just exclude it from the conditional formatting.
Or am I also misreading the question?

Cornucopia

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).

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?

Chino

DoaM wrote: »

what makes a matching cell a duplicate and what makes a matching cell a matching value? How are the two things different?

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.