Conditional Formatting Based on Two Columns for duplicates

[dancer5]

I'm running into errors with my conditional formatting. It's highlighting as a duplicate when it's actually not, but it's doing it inconsistently.

I'm looking to highlight the cells in column C and D when there is a duplicate across both columns.

Here's what I have as my conditional formatting formula:

=COUNTIFS($C$2:$C$1000,$C2,$D$2:$D$1000,$D2)>1

Conditional Duplicates.xlsx
	A	B	C	D	E
1	Done	Quarter	ID	#	Date
2	Yes	Q1	23-235007	1	11-May-22
3	Yes	Q2	23-230030	1	16-Sep-22
4	Yes	Q1	23-230029	1	23-Feb-22
5	Yes	Q2	11-110034	1	28-Mar-22
6	No	N/A	29-290007	1	14-Apr-22
7	Yes	Q2	29-290007	2	31-May-22
8	Yes	Q1	29-290023	1	1-Jun-22
9	Yes	Q2	23-235012	1	11-May-22
10	Yes	Q2	29-290016	2	8-Jun-22
11	No	N/A	11-110014	1	14-Jun-22
12	Yes	Q1	11-110014	2	14-Jun-22
13	No	N/A	11-110014	3	14-Jun-22
14	Yes	Q2	20-200006	1	7-Jul-22
15	Yes	Q1	29-290037	1	11-Jul-22
16	Yes	Q2	88-015005	1	10-Aug-22
17	Yes	Q2	88-010069	1	11-Aug-22
18	Yes	Q3	20-200007	1	11-Aug-22
19	No	N/A	19-195004	1	17-Aug-22
20	No	N/A	19-195002	2	17-Aug-22
21	Yes	Q3	11-110057	1	17-Aug-22
22	Yes	Q2	23-230049	1	17-Aug-22
23	Yes	Q2	88-010155	1	18-Aug-22
24	No	N/A	88-010092	1	18-Aug-22
25	PE	Q3	20-205028	1	25-Aug-22
26	No	N/A	20-200017	1	31-Aug-22
27	Yes	Q1	88-010125	1	17-Nov-22
28	Yes	Q2	23-230030	1	16-Sep-22
29	Yes	Q2	29-290035	1	22-Sep-22
30		Q4	29-290035	2	3-Mar-23
31	No	N/A	11-110059	1	27-Sep-22
32	Yes	Q2	11-110059	2	27-Sep-22
33	No	N/A	11-110059	1	28-Sep-22
34	No	N/A	29-290045	1	5-Oct-22
35	Yes	Q2	29-290045	2	5-Oct-22
36	No	N/A	29-290045	3	5-Oct-22
37	No	N/A	29-290045	4	5-Oct-22
38	No	N/A	88-010124	1	17-Nov-22
39	No	N/A	20-205012	1	12-Oct-22
40	Yes	Q2	20-205012	2	12-Oct-22
41	No	N/A	20-205012	3	12-Oct-22
42	Yes	Q1	88-015015	1	19-Oct-22
43	Yes	Q1	17-170016	1	19-Oct-22
44	Yes	Q1	88-015004	1	16-Aug-22
45	No	N/A	20-205001	1	28-Oct-22
46	No	N/A	17-170005	1	1-Nov-22
47	Yes	Q2	17-170005	2	7-Dec-22
48	Yes	Q2	17-170010	1	9-Nov-22
49	Yes	Q2	11-115017	2	4-Nov-22
50	Yes	Q2	11-115020	1	15-Nov-22
51	Yes	Q2	88-015029	1	21-Nov-22
52	No	N/A	88-015029	2	21-Nov-22
53	No	N/A	88-015029	3	21-Nov-22
54	Yes	Q1	29-290056	1	16-Dec-22
55	Yes	Q2	88-015028	1	21-Nov-22
56	Yes	Q2	88-010133	1	21-Nov-22
57	No	N/A	88-010133	2	21-Nov-22
58	No	N/A	88-015030	1	22-Nov-22
59	No	N/A	88-015030	2	22-Nov-22
60	No	N/A	88-015030	3	22-Nov-22
61	No	N/A	88-015030	4	22-Nov-22
62	No	N/A	88-015030	5	22-Nov-22
63	No	N/A	88-015030	6	22-Nov-22
64	Yes	Q2	88-010135	1	22-Nov-22
65	Yes	Q2	88-010156	1	29-Nov-22
66	No	N/A	88-010156	2	29-Nov-22
67			88-010156	3	17-Feb-23
68	Yes	Q3	88-010147	1	5-Dec-22
69	No	N/A	88-015027	1	23-Nov-22
70	Yes	Q2	29-295007	1	16-Dec-22
71	Yes	Q2	17-175002	1	8-Dec-22
72	No	N/A	17-175002	2	8-Dec-22
73	Yes	Q2	17-175003	2	14-Dec-22
74	Yes	Q2	17-175023	1	15-Dec-22
75	Yes	Q1	21-215027	1	7-Feb-23
76	Yes	Q1	11-110027	1	27-Feb-23
77			11-110027	2	27-Feb-23
78			17-175010	1	7-Feb-23
79			17-175010	2	7-Feb-23
80			11-110055	1	16-Feb-23
81			11-115038	1	17-Feb-23
82			11-115038	2	17-Feb-23
83			17-170008	1	23-Feb-23
84			11-110032	1	27-Feb-23
85			11-110032	2	27-Feb-23
86			17-170031	1	1-Mar-23
87			29-290068	1	9-Mar-23
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C1:D1,C74:D74	Expression	=COUNTIFS($C$2:$C$886,#REF!,$D$2:$D$886,#REF!)>1	text	NO
C2:D73,C75:D87	Expression	=COUNTIFS($C$2:$C$886,$C3,$D$2:$D$886,$D3)>1	text	NO

 

[Joe4]

I think your formula is off one column.

You have the following formula being applied starting in row 2:

=COUNTIFS($C$2:$C$886,$C3,$D$2:$D$886,$D3)>1

I think you may need to change it to this:

=COUNTIFS($C$2:$C$886,$C2,$D$2:$D$886,$D2)>1

since you are starting on row 2 (your formula needs to reflect the first row you are starting on).

 

[dancer5]

Thanks. That seems to have worked. I don't know how it got changed as I inputted C2 and D2. The range was initially C:D as well.

 

[Joe4]

You are welcome. Glad I was able to help!

When Conditional Formatting seems to be a little "off", that is often the issue (formula and range don't line up perfectly).
So that is always a good thing to check/confirm.