conditional formatting cells that have a match in another column

[jbrown021286]

Book3
	A	B	C	D	E
5	list	ro	tag	job	notes
6	6208414	6206519	2760	motor	wip
7	6208395	6206595	4102	motro	odyssey motor
8	6208383	6208155	2897	control arm and subframe
9	6208155	6208383	4529	micu and keyless control unit	wok
10	6206595	6208395	5710	done
11	6206519	6208414	6538	done	done
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B6	Expression	=COUNTIF(A:A,D6)>0	text	NO

i am looking to use a formula for conditional formatting. what i am wanting to do is highlight cells in column a that have a matching value in column b and visa Vesa. i am also wanting to highlight any cell in column a that does not have a match in column b (obviously in a different color). any help would be appreciated.

 

[Joe4]

You are on the right track. For matches, I would use a formula like:

=AND(D6<>"",COUNTIF(A:A,D6)>0)

And for non-matches a new rule that looks something like:

=AND(D6<>"",COUNTIF(A:A,D6)=0)

 

[jbrown021286]

Book3
	A	B	C	D	E
5	list	ro	tag	job	notes
6	6208414	6206519	2760	motor	wip
7	6208395	6206595	4102	motro	odyssey motor
8	6208383	6208155	2897	control arm and subframe
9	6208155	6208383	4529	micu and keyless control unit	wok
10	6206595	6208395	5710	done
11	6206519	6208414	6538	done	done
12	1
13		1
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A6:A38	Expression	=AND(B1048576<>"",COUNTIF(A:A,B1048576)=0)	text	NO
B6:B38	Expression	=AND(A6<>"",COUNTIF(B:B,A6)>0)	text	NO
A6:A38	Expression	=AND(B6<>"",COUNTIF(A:A,B6)>0)	text	NO

looks like i did not apply it correctly where is my mistake?

 

[Joe4]

Look at your first formula. Your formula needs to line-up with the first cell in your selection (cell A6).

So the formula should be referencing row number 6, not row number 1048576.

 

[jbrown021286]

Book3
	A	B	C	D	E
5	list	ro	tag	job	notes
6	1	18	2760	motor	wip
7	12	14	4102	motro	odyssey motor
8	14	27	2897	control arm and subframe
9	16	47	4529	micu and keyless control unit	wok
10	18		5710	done
11			6538	done	done
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
Sheet1 (2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A6:A38	Expression	=AND(B6<>"",COUNTIF(A:A,B6)=0)	text	NO
B6:B38	Expression	=AND(A6<>"",COUNTIF(B:B,A6)>0)	text	NO
A6:A38	Expression	=AND(B6<>"",COUNTIF(A:A,B6)>0)	text	NO

i still dont seem to have it set up right. what i am wanting it to do is highlight in green the cells in column a that have a matching cell anywhere in column b. and the same for column b to column a then any cell without a matching value in column a to be highlighted red.

 

[Joe4]

I think you have the range references your formulas backwards.

For example for the first one, to apply to cells A6:A38, I think the formula should be:

=AND(A6<>"",COUNTIF(B:B,A6)=0)

and likewise for the other two.

 

[jbrown021286]

I think you have the range references your formulas backwards.

For example for the first one, to apply to cells A6:A38, I think the formula should be:

=AND(A6<>"",COUNTIF(B:B,A6)=0)

and likewise for the other two.

that got it thanks for the help

 

[Joe4]

You are welcome.
Glad I was able to help!