Find if a number already exists in column

[dinkss]

Hi All,

I'm trying to get formula or macro to check if number already exists in a column G:G.
If an employee will enter the same number I would like to have a pop up message (Alert) that number entered already exists.

 

[dinkss]

You could just leave the code "as-is" and just use the Conditional Formatting I showed you in my initial posts to highlight the duplicates yellow.
The advantage to using Conditional Formatting is if you fix the issue, the color will go away automatically. Otherwise, you would need to add more code to your VBA code to "undo" the highlighting if the duplicate is fixed.

OK, I will leave it with conditional formatting Joe4.

Safest & easiest.

Thank you very much for your help.

 

[dinkss]

You could just leave the code "as-is" and just use the Conditional Formatting I showed you in my initial posts to highlight the duplicates yellow.
The advantage to using Conditional Formatting is if you fix the issue, the color will go away automatically. Otherwise, you would need to add more code to your VBA code to "undo" the highlighting if the duplicate is fixed.

I just tried Conditional formatting but it highlights rows even though they are not duplicates?
Am I doing something wrong?

 

[Joe4]

Sorry, I have been out of town for a few days.

It sounds to me like you probably did not apply it correctly.
One of the most common errros people make if that the formula they use does NOT match the range they selected.
The Conditional Formatting formula MUST be written to reflect the very first cell in your SELECTED range.

If you can get it to work, please let us know the following:
- What range you first selected to apply the Conditional Formatting to (exact row and column addresses)
- What formula you entered

 

[dinkss]

Sorry, I have been out of town for a few days.

It sounds to me like you probably did not apply it correctly.
One of the most common errros people make if that the formula they use does NOT match the range they selected.
The Conditional Formatting formula MUST be written to reflect the very first cell in your SELECTED range.

If you can get it to work, please let us know the following:
- What range you first selected to apply the Conditional Formatting to (exact row and column addresses)
- What formula you entered

I used this formula =COUNTIF(G:G,G1)>1 and have selected range from $G$1:$G$5000

 

[Sufiyan97]

Change formula to

=COUNTIF($G$1:$G$5000,G1)>1

 

[dinkss]

Change formula to

=COUNTIF($G$1:$G$5000,G1)>1

I tried that but it still shows - highlighting entries that are not duplicates? Am I missing something?

 

[Joe4]

Does this fix your issue:

=AND($G1<>"",COUNTIF($G$1:$G$5000,$G1)>1)

If not, can you please post a screen a screen image of an example to show us what you are talking about?

Also, here is one other things that may help us figure out what is going on.
First, remove ALL Conditional Formatting from your sheet.
Then, turn on your Macro Recorder (View --> Macros --> Record Macro), and re-apply the Conditional Formatting rules you are trying to do here.
Then, stop your Macro Recorder, and go into the VB Editor and copy/paste the VBA code that Macro Recoder just recorded here for us to see.
This will show us exactly how you are setting up, and any minor detailsd should be exposed.

 

[dinkss]

I

Does this fix your issue:

=AND($G1<>"",COUNTIF($G$1:$G$5000,$G1)>1)

If not, can you please post a screen a screen image of an example to show us what you are talking about?

Also, here is one other things that may help us figure out what is going on.
First, remove ALL Conditional Formatting from your sheet.
Then, turn on your Macro Recorder (View --> Macros --> Record Macro), and re-apply the Conditional Formatting rules you are trying to do here.
Then, stop your Macro Recorder, and go into the VB Editor and copy/paste the VBA code that Macro Recoder just recorded here for us to see.
This will show us exactly how you are setting up, and any minor detailsd should be exposed.

I will do that. Thank you so much!

 

[dinkss]

Does this fix your issue:

=AND($G1<>"",COUNTIF($G$1:$G$5000,$G1)>1)

If not, can you please post a screen a screen image of an example to show us what you are talking about?

Also, here is one other things that may help us figure out what is going on.
First, remove ALL Conditional Formatting from your sheet.
Then, turn on your Macro Recorder (View --> Macros --> Record Macro), and re-apply the Conditional Formatting rules you are trying to do here.
Then, stop your Macro Recorder, and go into the VB Editor and copy/paste the VBA code that Macro Recoder just recorded here for us to see.
This will show us exactly how you are setting up, and any minor detailsd should be exposed.

I have removed ALL Conditional Formatting from my sheet.
I have tried conditional formatting with all your formulas, latest was: =AND($G1<>"",COUNTIF($G$1:$G$5000,$G1)>1) - unfortunately didn't work either

Below is the MACRO code that was recorded:

Sub Macro1()
'
' Macro1 Macro
'

'
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND($G1<>"",COUNTIF($G$1:$G$5000,$G1)>1)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
End Sub

Also attached screenshots when conditional formatting is applied - Excel is highlighting random cells?

 

[Joe4]

It looks like you have not cleared out the old Conditonal Formatting rules first.
Notice how your Conditional Formula code is using yellow highlighting (color 65535), but your screen image shows amber highlighting.
So that color from your image is NOT coming from this new conditional formatting rule. You have old stuff or manual coloring still in there!

This code will first remove all old Conditional Formatting rules. If you still see amber coloring on cells, then that is probably manual coloring you have added to the worksheet.

Sub Macro2()
   
'   Remove all Conditional Formatting from sheet
    Cells.FormatConditions.Delete
    
'   Apply conditional formatting formula
    With Range("G1:G5000")
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
            "=AND($G1<>"""",COUNTIF($G$1:$G$5000,$G1)>1)"
        .FormatConditions(.FormatConditions.Count).SetFirstPriority
        With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 65535
            .TintAndShade = 0
        End With
        .FormatConditions(1).StopIfTrue = False
    End With
    
End Sub