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

  • Thread starter Thread starter Anitacr
  • Start date Start date
A

Anitacr

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!
 
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.value) > 1 then
'Apply conditional format
end if
end if
next r
 
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!
Click to expand...

Just use application.worksheetfunction.countif(range,cell) > 1 to determine if
the cells are duplicated.
--ron
 
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 formatting>formula 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
 
Back
Top