I'm trying to add data validation to columns D to U where the only value allowed is either blank or 1, when there is a name in column a of the same row - except column F, it should be allowed to have any whole number greater than or equal to 1.
So far I've recorded a macro and tried to reverse engineer it.... here's what I've got so far, but it doesn't work...
also, since i was there, i was also trying to center the text because it doesn't default that way...
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Selection.Column = 1 Then
If Range("a" & Selection.Row) <> "" Then
Range("d" & Selection.Row, "u" & Selection.Row).Select
With Selection.Validation
.Delete
.Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _
Operator:=xlEqual, Formula1:="1"
.IgnoreBlank = True
.ErrorTitle = "Invalid Entry!"
.ErrorMessage = "Use 1 to indicate fee or entry into event."
.ShowInput = False
.ShowError = True
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
Range("f" & Selection.Row).Select
With Selection.Validation
.Delete
.Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _
Operator:=xlGreaterEqual, Formula1:="1"
.IgnoreBlank = True
.ErrorTitle = "Invalid Entry!"
.ErrorMessage = "Use 1 or more to indicate number of time onlys. Blank cell for none."
.ShowInput = False
.ShowError = True
End With
Else
Range("d" & Selection.Row, "u" & Selection.Row).Select
With Selection.Validation
.Delete
.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
:=xlEqual
.IgnoreBlank = True
End With
End If
End If
End Sub
Any help would be most appreciated.
Thank you in advance.
Bookmarks