Highlighting duplicated values using conditional formatting rule with formula

[INN]

Hi
I have 2 columns and I want to highlight duplicated values (using formula, I know there is an option to highlight without using formula).
So I highlighted these 2 ranges A1:A7 and B1:B7 and then wrote this formula in conditional formatting-->Formula

=COUNTIF(A1,$B1)>1

Appreciate any help. Thank you very much.

ID	ID
1​	1​
2​	3​
3​	5​
4​	7​
5​	9​
6​	11​

 

[Fluff]

How about

Fluff.xlsm
	A	B
1	ID	ID
2	1	1
3	2	3
4	3	5
5	4	7
6	5	9
7	6	11
Sheet3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B7	Expression	=COUNTIFS(A:A,B2)	text	NO
A2:A7	Expression	=COUNTIFS(B:B,A2)	text	NO

 

[INN]

Wow, thank you very much. Appreciate the help.

 

[Vogateer]

I'm not clear on whether you want both Columns to be highlighted if there's a duplicate or just one of the columns.

Try highlighting the data cells ($A$2:$B$7) and enter this formula:

=COUNTIF($A$2:$B$7, A2)>1

This will highlight any value in both columns that is found in another cell within those two columns.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[C Moore]

Name your range, dynamically if needed. Be sure the cell reference in the formula is the active cell in the selected region. *took too long