only allow 1 or 2 in a cell range / date

[Trevor3007]

test_servey.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
5	Wing	Floor	Desk	Dock	USB3	K/B	Mouse	Monitor	HDMI/DVI	DVI/DVI	Note / Comments	Date
6				1	1	1	1	1	1	1	Full Kit
7				2	2	2	2	2	2	2	No Kit
8				1	2	1	1	1	2	1	Part Kit
Site Survey

Cells with Data Validation
Cell	Allow	Criteria
K6:K8	List	=DropDown!$A$2:$A$4

Good evening,

I have 3 issues:-

1) in the cells that have 1 or a 2, i want to force the user only to use 1 or 2 in the respective area (d6-j999 .and should the user not use 1 or 2 in the respective cells, a popup message will appear stating the fact .
2+3) in the date which MUST be entered when , can the date be triggered so when there is data in the range k6-k999.
I cannot use ()now as it will change the date each each day. So can it be done so the date will automatically enter, but not change when the 'system' date does?

The above is well out of my scope & therefore reaching out to Mr Excel for help.

Thank you for your assistance with my issue.

 

[Trevor3007]

I see, it's supposed to go in here ...

    Const BINARY_RANGE      As String = "d6:J999"
    Const COMMENTS_RANGE    As String = "K6:K999"

    Const PLACEHOLDER       As String = "$@#@$"
    Const MESSAGE           As String = "Cell $@#@$ Only 1 Is Allowed!"
   
    Dim Act As Boolean
    Dim c   As Range
   
    Application.EnableEvents = False
    For Each c In Target
        Act = False
        If Not Application.Intersect(c, Range(BINARY_RANGE)) Is Nothing Then
            If IsError(c.Value) Then
                Act = True
            ElseIf c.Value = vbNullString Then
                ' do nothing
            Else
                If c.Value <> 0 And c.Value <> 1 Then
                    Act = True
                End If
            End If
            If Act Then
                c.Value = vbNullString
                MsgBox Replace(MESSAGE, PLACEHOLDER, c.Address)
            End If
        End If
    Next c
    For Each c In Target
        If Not Application.Intersect(c, Range(COMMENTS_RANGE)) Is Nothing Then
            If IsError(c.Value) Then
                c.Offset(0, 1).Value = vbNullString
            Else
                If Len(c.Value) = 0 Then
                    c.Offset(0, 1).Value = vbNullString
                Else
                    c.Offset(0, 1).Value = Date
                End If
            End If
        End If
    Next c
    Application.EnableEvents = True

thanks .. that works great.

have good day & best wishes too.