Prevent Duplicate "X" or number VBA script

[ipon70]

I have an issue, where a column could have an "X" or any number from "2,3,4,5" in the cell. I need to prevent them from putting an X or anything else in the same column,so two x's would not be good, or two 2's, or even an X and a 2. That would be F18:F34 but the entire sheet is actually F18:AU34.
I can't use data validation as it is used up by another function.

Excel 2016

 

[mole999]

it won't prevent but conditional format will highlight duplicates

 

[ipon70]

So I can't use conditional as there are 7 duplicates of that area per sheet, and 13 sheets.....so I need a little better solution then highlighting an 20 X 44 grid and breaking it up into 44 individual conditional format rules.

 

[MickG]

This is some code that relates to your first post requirements:-

Private [COLOR="Navy"]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]Dim[/COLOR] oVal [COLOR="Navy"]As[/COLOR] Variant, Rng [COLOR="Navy"]As[/COLOR] Range, dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Dic [COLOR="Navy"]As[/COLOR] Object, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
[COLOR="Navy"]With[/COLOR] Dic
.Add "X", "": .Add "x", "": .Add 2, "": .Add 3, "": .Add 4, "": .Add 5, ""
[COLOR="Navy"]End[/COLOR] With

Application.EnableEvents = False
[COLOR="Navy"]If[/COLOR] Not Intersect(Range("F18:AU34"), Target) [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Intersect(Range("F18:AU34"), Range(Cells(18, Target.Column), Cells(34, Target.Column)))
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] dn [COLOR="Navy"]In[/COLOR] Rng
        [COLOR="Navy"]If[/COLOR] Dic.Exists(dn.Value) [COLOR="Navy"]Then[/COLOR] c = c + 1
        [COLOR="Navy"]If[/COLOR] c > 1 [COLOR="Navy"]Then[/COLOR] Target.Value = "": [COLOR="Navy"]Exit[/COLOR] For
    [COLOR="Navy"]Next[/COLOR] dn
[COLOR="Navy"]End[/COLOR] If
Application.EnableEvents = True
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[ipon70]

Thank you MickG this worked. Awesome work, sorry it took me so long to get back to you, this got me on the right track and helped with another section of this project also.

 

[MickG]

You're welcome