Color code on another cell if cell has duplicate value

[aayaanmayank]

Hi All,

Can anyone help me with. i have below data, if ID is duplicate then do the green color on next cell else yellow color...

[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 64"]ID[/TD]
[TD="class: xl63, width: 64"]Country name[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]306560 [/TD]
[TD="class: xl64"]US[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]4569087 [/TD]
[TD="class: xl64"]US[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]896880 [/TD]
[TD="class: xl64"]US[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]896880 [/TD]
[TD="class: xl64"]KR[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]9970980 [/TD]
[TD="class: xl64"]US[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]9970980 [/TD]
[TD="class: xl64"]US[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]423489 [/TD]
[TD="class: xl64"]US[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]8076070 [/TD]
[TD="class: xl64"]US[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]8858780 [/TD]
[TD="class: xl64"]US[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]98724[/TD]
[TD="class: xl64"]US[/TD]
[/TR]
</tbody>[/TABLE]

 

[etaf]

i would use 2 conditional rules

rule 1 for any duplicate - yellow
=COUNTIF(A2:A11,A2)>1

rule 2 for next cell - green - use and put last in the order
=A2=A1

 

[etaf]

use
=OR(A1=A2,A2=A3)

[TABLE="width: 174"]
<colgroup><col span="2"></colgroup><tbody>[TR]
[TD]ID[/TD]
[TD]Country name[/TD]
[/TR]
[TR]
[TD="align: right"]306560[/TD]
[TD]US[/TD]
[/TR]
[TR]
[TD="align: right"]4569087[/TD]
[TD]US[/TD]
[/TR]
[TR]
[TD="align: right"]896880[/TD]
[TD]US[/TD]
[/TR]
[TR]
[TD="align: right"]896880[/TD]
[TD]KR[/TD]
[/TR]
[TR]
[TD="align: right"]9970980[/TD]
[TD]US[/TD]
[/TR]
[TR]
[TD="align: right"]9970980[/TD]
[TD]US[/TD]
[/TR]
[TR]
[TD="align: right"]423489[/TD]
[TD]US[/TD]
[/TR]
[TR]
[TD="align: right"]8076070[/TD]
[TD]US[/TD]
[/TR]
[TR]
[TD="align: right"]8858780[/TD]
[TD]US[/TD]
[/TR]
[TR]
[TD="align: right"]4569087[/TD]
[TD]US[/TD]
[/TR]
</tbody>[/TABLE]

 

[aayaanmayank]

thanks but i need VBA code

 

[etaf]

oh, OK - probably useful to have put that in the subject/title