Highlighting cell contents, based on a range

[wherethegoalsat]

Hi

I am trying to work out how to create a formula/conditional formatting for duplication. The conditions are as follows. (from the image)

1. Grey box is know as 'contents', Yellow box is 'New Lot'.
2. So IF the cell contents, in the yellow box matches ANY of the cell contents in the grey box, then BOTH of the cells would highlight (so red text for example).
3. If there are 2 matching cells in the grey box, then it WONT highlight the cells, as its only concerned with a match in the yellow box

In the example in the picture, i would expect to see
* fridge highlighted in cells A4 & A12
* saw highlighted in cells C5 & A14
* bed in cells C6 & A7 to NOT be highlighted

The thinking long term, is that the yellow 'new lot' section will constantly change and i can see whats a duplicate of the contents (grey section.).

Hope this made sense... not sure if its achievable.

 

[wherethegoalsat]

Try this

here is the working file to download:

CondiFormat.zip

Book6.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1		House	Garage	Shed
2
3
4		Cooker	Bench	lawnmower
5		Fridge	Bike	Bike
6		Shelf	TV	Saw
7		Computer		Bed
8		Bed	Light	Light
9										Yansa
10
11
12
13
14
15
16
17
18
19
20
21		bed
22		Fridge
23		Light
24		saw
25		Bowl
26		Yansa
27
28
29
30
Sheet4 (2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B21:B999	Expression	=SUM(COUNTIF(B21, $B$4:$K$18))=1	text	NO
B4:K17	Expression	=SUM(COUNTIF(B4, $B$21:$B$999))*(SUM(COUNTIF(B4, $B$4:$K$18))<2)	text	NO

thank you very much for your help sir.

 

[felixstraube]

You are welcome. Thanks for the feedback