Finding duplicate numbers

[wookmaster]

Is there a way to make a macro or something that will search through an
excel sheet and see if there are any duplicate numbers. Thanks for any
help

 

[Paul B]

wookmaster, here is a post by Chip Pearson to do what is selected

Use the following VBA code:

Sub MakeDupsRed()
Dim Rng As Range
For Each Rng In Selection.Cells
If Application.WorksheetFunction.CountIf( _
Selection, Rng) > 1 Then
Rng.Interior.ColorIndex = 3 'red
Else
Rng.Interior.ColorIndex = xlColorIndexNone
End If
Next Rng
End Sub

Select the range of cells and run the macro.
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)



--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2000 & 97
** remove news from my email address to reply by email **

 

[Mas]

Thanks for the tip and it works a treat. I would like to tweak it a little
and I was wondering is it possible to change the color of the font (text)
instead of highlighting the cells?

Thanks in advance.

M

wookmaster, here is a post by Chip Pearson to do what is selected

Use the following VBA code:

Sub MakeDupsRed()
Dim Rng As Range
For Each Rng In Selection.Cells
If Application.WorksheetFunction.CountIf( _
Selection, Rng) > 1 Then
Rng.Interior.ColorIndex = 3 'red
Else
Rng.Interior.ColorIndex = xlColorIndexNone
End If
Next Rng
End Sub

Select the range of cells and run the macro.
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)



--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2000 & 97
** remove news from my email address to reply by email **

 

[Frank Kabel]

Hi Mas
change the lines
....
Rng.Interior.ColorIndex = 3 'red
Else
Rng.Interior.ColorIndex = xlColorIndexNone
....
to
Rng.Font.ColorIndex = 3 'red
Else
Rng.Font.ColorIndex = xlColorIndexNone


HTH
Frank

 

[Frank Kabel]

Sorry
replace xlColorIndexNone with xlColorIndexAutomatic
Frank

 

[M]

Thanx Frank ... it worked perfectly.

M

Sorry
replace xlColorIndexNone with xlColorIndexAutomatic
Frank

 

[Frank Kabel]

Hi
thanks for the thanks
Frank