hi all
i have created a very long drop list (25 values) for validating all
cells on column E, the same list appears on all the cells on the
columns G,I,K,M... and so forth.
is there a code that can check if in a row 2 or more cells in the
relevant columns contain the same Value.
and in such a case, for exmple change the color of the cells with the
same values.
thanks guy

Is there a possibilty that columns that are not checked (F, H, J, etc) will
have duplicate values as well? If not, the problem is fairly easy. If they
could, then it becomes more complex. this assumes the simpler case:

Sub FindDupes()
Dim cell as range, rng as Range
Dim cell1 as Range, rng1 as Range
set rng = Range("E2",Cells(rows.count,"E").End(xlup))
for each cell in rng
set rng1 = cell.resize(1,20) '<== change the 20
for each cell1 in rng1
if cell1.column mod 2 = 1 then
if application.countif(rng1,cell1) 1 then
cell1.Interior.ColorIndex = 3
else
cell1.Interior.ColorIndex = xlNone
end if
end if
Next cell1
Next cell
End sub

Change the 20 to reflect the number of columns to check.

--
Regards,
Tom Ogilvy


"Guye" wrote:

hi all
i have created a very long drop list (25 values) for validating all
cells on column E, the same list appears on all the cells on the
columns G,I,K,M... and so forth.
is there a code that can check if in a row 2 or more cells in the
relevant columns contain the same Value.
and in such a case, for exmple change the color of the cells with the
same values.
thanks guy

hi,
it gives me the following bug
Unable to set the Colorindex property of interior class
thanks

hi,
its the simple case
regards, guy

It ran like a champ for me against dummy data I would consider consistent
with your explanation. Hopefully you haven't done something silly like
protecting your worksheet.

--
Regards,
Tom Ogilvy



"Guye" wrote:

hi,
it gives me the following bug
Unable to set the Colorindex property of interior class
thanks

hi
its true, i have protected it.
when i unprotected it, it worked.
regards guy