problem with excell 2007

[EDUCATED MONKEY]

I am using office 2007 windows xp professional<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Yet another one of Microsoft’s bells and whistle that does not work!<o></o>
I have a worksheet column that has 90918 rows residing in column H, if you ask it to remove duplicates; it does so declaring in this case I had 1643 duplicates leaving 89275 unique values<o></o>
1643 + 89275 =90918 great all is well now try as many times as you like to get it to get conditional formatting to highlight them instead of deleting them, as I need to past the result next to the original column so enabling me to see at a glance where the duplicates are. You will find it does not work, at first I had the idea that it would take it some time to perform that action as it would need to check 90918 values against each other a couple of hours maybe even with my 3g processors 2 days later just hanging I had to use control alt delete to close excel<o></o>
So clearly I had to think again and came up with the following vba although this may not be best practice the results look favourable, except for my error of making the cell black by put declaring <o></o>
Interior.Color =24 instead of Interior.Color= 3 to make it red <o></o>
Now even though I have changed that line of code to the correct value it still insists on making it black!<o></o>
I have closed excel reopened it to no avail will some one please tell how to reset the vba?<o></o>
Or how to make the conditional formatting work<o></o>

Sub COlourDups()
'
' COlourDups Macro
'
' Keyboard Shortcut: Ctrl+c
'
Dim Mystring1 As String
Dim Mystring2 As String
Dim n As Long
For n = 1 To 5  ' test only will be full range later
ActiveWorkbook.Sheets("dups").Activate
Range("H1").Select
 
 Mystring1 = Worksheets("dups").Cells(n, 8).Value
 Mystring2 = Worksheets("dups").Cells(n + 1, 8).Value
 
 If Mystring1 = Mystring2 Then
 ActiveCell.Interior.Color = 3
 End If
 
 Next n
 
End Sub

One other point before I forget I used the sort a to z fist so duplicates would reside under each other other wise the vba would not work<o></o>

 

[Alan C]

I have to deal with a lot of duplicates in fairly large datasets and find that I want to view them before I delete. I simply name the range and in conditional formatting use the formula =COUNTIF(RANGE,A1)>1 and use a fill color that really pops. You can sort before or after, I don’t think it matters. If sorting after just bring the cell color(s) to the top. It’s accurate and I can prove it by using the advanced filter to move and then count unique values.

BTW, it works quickly on large sets.

Hopes this helps.

AC

 

[EDUCATED MONKEY]

thanks i will give that a try next time regards pete