This will add Data Validation to every cell in Column("T") when the sheet is activated.
I don't think you can prevent anyone from modifying the Data Validation except by locking the cells and protecting the sheet, but they can unprotect the sheet unless you have it password protected..
Adapt this to whatever you need.
Option Explicit
Private Sub Worksheet_Activate()
Dim lRow As Long
Dim rCell As Range
Dim lLastRow As Long
With Sheet2.Columns("T")
lLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
For lRow = 42 To lLastRow
With .Cells(lRow, 1).Validation
.Delete
.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
:=xlBetween
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = "Test"
.ErrorTitle = ""
.InputMessage = "Text = T06"
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Next lRow
End With
End Sub
If you need the Validtion Message to change when the cell is changed, then put the code into the Worksheet_Change() event instead.
Bookmarks