Highlight cells

[ChrisMac1]

Hi there,

I'd like to know the best way to highlight the data in the cells in a column on one tab, where the data matches a cell in another column on another sheet.

Simply, I need to highlight any numbers in Column A of sheet B, if the same number exists in column A of sheet A.

Any advise would be appreciated.

 

[etaf]

conditional formatting using a COUNTIF()

Select column A on sheet B

then use
COUNTIF( A!A:A, A1)>0

for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>
Select sheet B A:A - Change, reduce or extend the rows to meet your data range of rows

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:
=countif(A!A:A,A1)>0

Format [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK

Book1
	A
1
2
3
4	1
5	2
6
7
8
9	4
10
11
12	6
13
14
15
16	10
17
A

Book1
	A
1	1
2	2
3	3
4	4
5	5
6	6
7	7
8	8
9	9
10	10
11	11
12	12
13	13
14	14
15	15
16	16
17	17
18	18
19	19
20	20
21	21
22	22
23	23
B

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:A23	Expression	=COUNTIF(A!A:A,A1)	text	NO

 

[ChrisMac1]

Amazing, thanks so much etaf, worked a treat.

 

[etaf]

you are welcome