ExcelBanter - conditional formating

Some of the people that post to this forum have their own websites for tips (I don't).

Some of them even have it in their signatures on their posts.

Here are a couple:

http://www.mvps.org/links.html#Excel

www.cpearson.com

Good luck in your future programming!

"souris75" wrote in message ...
I had to change so much things in the past 6 hrs...Thank you so much! By the way can you tell me where I can find some tips and maybe training on vba? That would be probably a must in my case right now! Many tks!

----- Medemper wrote: -----

Just to make sure, your initial question said you had a huge amount of numbers in the C column.

I don't know how well this wraps on your screen, so be careful when copying (I added some comments to some of the rows).

Assuming the numbers are in column B starting on row 5:

Sub FindAndColorDuplicates()
Range("b5").Select
Selection.End(xlDown).Select 'find last row in list (before blank)
CountofCells = Range(Range("b5"), ActiveCell).Count 'Count number of rows
Range("b6").Select
For i = 6 To CountofCells 'start in row 6 to end
For j = i - 1 To 5 Step -1 'stepping backwards back to row 5
If Cells(i, 2).Value = Cells(j, 2).Value Then '2 is Column B
Cells(i, 2).Font.ColorIndex = 3 ' 3=red
Exit For 'if finds ones it exits j loop
End If
Next j
Next i
End Sub

"SOURIS75" wrote in message ...
I know a little bit about macro but the one who are made directly by excel... anyway i'm learning this is great! But now can you help me to change something? My numbers are starting at B5 until... is it possible? Thank you so much... my hair are growing back a little bit!

----- Medemper wrote: -----

Sorry. It goes in a Module in Visual Basic Editor. II am assuming XL2000 (may be same for other versions). If you click Tools, Macro, Visual Basic editor. On the left side will show the name of your workbook and list your sheets underneath it. If you right click on the name of your workbook, click Insert, Module. Now underneath all your sheets, it will say Modules, Module 1. Double click on Module 1. Then copy paste that macro I wrote into the screen on the right. you should now be able to close the Microsoft Visual Basic Editor.

The macro will now be available to you under Tools, Macro, Macros. I would definitely make a copy of your Workbook before running this, just in case it's slow and you need to End Task it (better safe than sorry).

Again, i'm sorry. i thought you knew about Macros and just needed one for doing this checking for you.

"souris75" wrote in message ...
I'm not very good with visual basic and don't even know where to put this! but tks maybe i will be able to work it out!

----- Medemper wrote: -----

Heres a way after-the-fact to check and mark cells in C column.

This assumes numbers start in C1 and have no blanks until end.

Haven't tried it on "huge" amount of numbers, so it will probably be slow.

Sub FindAndColorDuplicates()
Range("c1").Select
Selection.End(xlDown).Select
CountofCells = Range(Range("c1"), ActiveCell).Count
Range("c2").Select
For i = 2 To CountofCells
For j = i - 1 To 1 Step -1
If Cells(i, 3).Value = Cells(j, 3).Value Then
Cells(i, 3).Font.ColorIndex = 3 ' 3=red
Exit For 'if finds ones it exits j loop
End If
Next j
Next i
End Sub