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]

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

Orange is manual coloring of the cells, so that is why I'm finding it strange as it highlights cells that are not duplicates.
I don't know what is happening there.

 

[Joe4]

Orange is manual coloring of the cells, so that is why I'm finding it strange as it highlights cells that are not duplicates.
I don't know what is happening there.

Where do you see it highlighting ANYTHING in yellow on your sheet?
That amber coloring is due to your manually coloring, not the Conditional Formatting.
As soon as you start entering real data, it highlights the duplicates in yellow, leaving the rest of the cells in the amber color, i.e.

This is expected behavior. So I am not sure what the issue still is.
How should this behave any differently? It seems to do exactly what you are asking.