conditional formating

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi I have a huge amount of number to enter in the C column. I want to be sure to not enter the same number twice. Is it possible to have a formula who can retrace the number (if it's enter twice) and to change the color of this number? It's been almost 2 days.... and by the way

NB.SI (C:C;C1)=1 is not working even in conditional formating or in validation
Thank you.
 
Formula is: =COUNTIF(C:C,C1)>1

Regards

Trevor


souris75 said:
Hi I have a huge amount of number to enter in the C column. I want to be
Click to expand...
sure to not enter the same number twice. Is it possible to have a formula
who can retrace the number (if it's enter twice) and to change the color of
this number? It's been almost 2 days.... and by the way:
 
See Chip Pearson's website at
http://www.cpearson.com/excel.htm
He has a way to identify duplicate entries in an array of
data.

-----Original Message-----
Hi I have a huge amount of number to enter in the C
Click to expand...
column. I want to be sure to not enter the same number
twice. Is it possible to have a formula who can retrace
the number (if it's enter twice) and to change the color
of this number? It's been almost 2 days.... and by the way:
 
I'm probably dum but it's not working... sni

----- Joe Adams wrote: ----

See Chip Pearson's website at
http://www.cpearson.com/excel.htm
He has a way to identify duplicate entries in an array of
data

-----Original Message----
Hi I have a huge amount of number to enter in the C
Click to expand...
column. I want to be sure to not enter the same number
twice. Is it possible to have a formula who can retrace
the number (if it's enter twice) and to change the color
of this number? It's been almost 2 days.... and by the way
 
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
Hi I have a huge amount of number to enter in the C column. I want to be sure to not enter the same number twice. Is it possible to have a formula who can retrace the number (if it's enter twice) and to change the color of this number? It's been almost 2 days.... and by the way:

NB.SI (C:C;C1)=1 is not working even in conditional formating or in validation.
Thank you.
 
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").Selec
Selection.End(xlDown).Selec
CountofCells = Range(Range("c1"), ActiveCell).Coun
Range("c2").Selec
For i = 2 To CountofCell
For j = i - 1 To 1 Step -
If Cells(i, 3).Value = Cells(j, 3).Value The
Cells(i, 3).Font.ColorIndex = 3 ' 3=re
Exit For 'if finds ones it exits j loo
End I
Next
Next
End Su
Hi I have a huge amount of number to enter in the C column. I want to be sure to not enter the same number twice. Is it possible to have a formula who can retrace the number (if it's enter twice) and to change the color of this number? It's been almost 2 days.... and by the way

NB.SI (C:C;C1)=1 is not working even in conditional formating or in validation
Thank you
 
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.


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
 
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


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").Selec
Selection.End(xlDown).Selec
CountofCells = Range(Range("c1"), ActiveCell).Coun
Range("c2").Selec
For i = 2 To CountofCell
For j = i - 1 To 1 Step -
If Cells(i, 3).Value = Cells(j, 3).Value The
Cells(i, 3).Font.ColorIndex = 3 ' 3=re
Exit For 'if finds ones it exits j loo
End I
Next
Next
End Su
 
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

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.


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
 
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").Selec
Selection.End(xlDown).Select 'find last row in list (before blank
CountofCells = Range(Range("b5"), ActiveCell).Count 'Count number of row
Range("b6").Selec
For i = 6 To CountofCells 'start in row 6 to en
For j = i - 1 To 5 Step -1 'stepping backwards back to row
If Cells(i, 2).Value = Cells(j, 2).Value Then '2 is Column
Cells(i, 2).Font.ColorIndex = 3 ' 3=re
Exit For 'if finds ones it exits j loo
End I
Next
Next
End Su

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


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").Selec
Selection.End(xlDown).Selec
CountofCells = Range(Range("c1"), ActiveCell).Coun
Range("c2").Selec
For i = 2 To CountofCell
For j = i - 1 To 1 Step -
If Cells(i, 3).Value = Cells(j, 3).Value The
Cells(i, 3).Font.ColorIndex = 3 ' 3=re
Exit For 'if finds ones it exits j loo
End I
Next
Next
End Su
 
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!

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

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.


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
 
Back
Top