Conditional formatting based on data from another sheet

[Mux99]

Hi all

I would like to highlight cells, based on data from another sheet if the text in column A matches.

Sheet1 contains data with names in column A.
Sheet2 also contains data with names but in a different order and some will be listed multiple times so these would all need to be highlighted.

Sheet1	A	B	C	D	E
1	apple
2	orange		X		X
3	peach	X		X
4	banana		X
5	melon

Sheet2	A	B	C	D	E
1	melon
2	orange		HIGHLIGHT		HIGHLIGHT
3	peach	HIGHLIGHT		HIGHLIGHT
4	orange		HIGHLIGHT		HIGHLIGHT
5	apple
6	peach	HIGHLIGHT		HIGHLIGHT
7	banana		HIGHLIGHT
8	orange		HIGHLIGHT		HIGHLIGHT
9	apple

Thanks in advance

 

[DanteAmor]

Select your data from sheet1 and create a named range, let's say: Fruits.

On sheet2 select cell B2.
In conditional format put this formula:
=VLOOKUP($A2,Fruits,COLUMN(),0)="X"

Select format color.

In Referes to:
=$B$2:$E$10

varios 08jul2020.xlsm
	A	B	C	D	E
1	Fruit	H1	H2	H3	H4
2	melon
3	orange
4	peach
5	orange
6	apple
7	peach
8	banana
9	orange
10	apple
Sheet2

 

[Mux99]

Select your data from sheet1 and create a named range, let's say: Fruits.

On sheet2 select cell B2.
In conditional format put this formula:
=VLOOKUP($A2,Fruits,COLUMN(),0)="X"

Select format color.

In Referes to:
=$B$2:$E$10

varios 08jul2020.xlsm
	A	B	C	D	E
1	Fruit	H1	H2	H3	H4
2	melon
3	orange
4	peach
5	orange
6	apple
7	peach
8	banana
9	orange
10	apple
Sheet2

Thanks. Worked perfectly

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.