[VBA Excel 2003] How to do it in VBA?
 

I need to verify in a column which cells are duplicated.
I don´t know how to do it in VBA, or the correct Excel´s function.
Example:

Column X

9675
9868
9868
9619
9868
9619
9619
9619
9619
9619

I want to identify the cells with same contents and mark them, with a
conditional format.

Anybody can help me?

Please!

--
Anitacr

[VBA Excel 2003] How to do it in VBA?
 

I think I'd do it something like this (completely untested)

Dim myRange as Range
Dim r as range
Dim lRow as long

Dim aWS as worksheet
Set aWS = activesheet

set myRange = aws.cells(1,1) 'Change to first cell in range to test
lRow = aws.cells(aws.rows.count,myrange.column).end(xlup) .row

Set myRange = myrange.resize(lrow-myrange.row + 1,1)

for each r in myrange
if not isempty(r) then
if application.worksheetfunction.countif(myrange,r.va lue) 1 then
'Apply conditional format
end if
end if
next r
--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.



"Anitacr" wrote:

I need to verify in a column which cells are duplicated.
I don´t know how to do it in VBA, or the correct Excel´s function.
Example:

Column X

9675
9868
9868
9619
9868
9619
9619
9619
9619
9619

I want to identify the cells with same contents and mark them, with a
conditional format.

Anybody can help me?

Please!

--
Anitacr

[VBA Excel 2003] How to do it in VBA?
 

On Thu, 9 Oct 2008 10:37:01 -0700, Anitacr
wrote:

I need to verify in a column which cells are duplicated.
I don´t know how to do it in VBA, or the correct Excel´s function.
Example:

Column X

9675
9868
9868
9619
9868
9619
9619
9619
9619
9619

I want to identify the cells with same contents and mark them, with a
conditional format.

Anybody can help me?

Please!

Just use application.worksheetfunction.countif(range,cell) 1 to determine if
the cells are duplicated.
--ron

[VBA Excel 2003] How to do it in VBA?
 

Don't use a macro its overkill for something simple, you can use conditional
formatting you can select the whole area to give it a name like range1 select
conditional formattingformula is, paste this in =COUNTIF(range1,A1)1 and
select your colour, now all duplicates will e highlighted!

Regards,
The Code Cage Team
www.thecodecage.com/forumz

"Anitacr" wrote:

I need to verify in a column which cells are duplicated.
I don´t know how to do it in VBA, or the correct Excel´s function.
Example:

Column X

9675
9868
9868
9619
9868
9619
9619
9619
9619
9619

I want to identify the cells with same contents and mark them, with a
conditional format.

Anybody can help me?

Please!

--
Anitacr