Private Sub Worksheet_SelectionChange(ByVal Target As Range)

With Range("PARTS1_PC1_1").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=PartsCategories"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = "Please use the drop-down menu to select your entry."
.ShowInput = True
.ShowError = True
End With


With Range("PARTS1_PC7_2:PARTS1_PC7_4").Validation
.Delete
if Application.CountA(Range("Parts1_PC1_1")) < 0 then
.Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="0", Formula2:="100"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.ErrorMessage = "Invalid entry; please enter a whole number."
.ShowInput = True
.ShowError = True
End if
End With

End Sub

If PARTS1_PC1_1 is actually a one cell range, then you can just move your IF
statement


With Range("PARTS1_PC7_2:PARTS1_PC7_4").Validation
.Delete
If Range("PARTS1_PC1_1").Value < "" Then
.Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="0", Formula2:="100"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.ErrorMessage = "Invalid entry; please enter a whole number."
.ShowInput = True
.ShowError = True
End If
End With



--
Regards,
Tom Ogilvy

"Paige" wrote in message
...
I have the following data validation code; the intent is to have data
validation in a cell with the range name of PARTS1_PC1_1 at all times
(which
the code does do-see the first section of code below), but data validation
in
the other cells ONLY if PARTS1_PC1_1 is not blank (see the second section
of
code below). The problem I have is that if PARTS1_PC1_1 is filled in and
then deleted, the data validation remains in the other cells - it doesn't
'reset' (i.e., clear out). Can you advise what I am doing wrong here?
Any
help would be greatly appreciated! Thanks....

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

With Range("PARTS1_PC1_1").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=PartsCategories"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = "Please use the drop-down menu to select your entry."
.ShowInput = True
.ShowError = True
End With

If Range("PARTS1_PC1_1").Value < "" Then
With Range("PARTS1_PC7_2:PARTS1_PC7_4").Validation
.Delete
.Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="0", Formula2:="100"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.ErrorMessage = "Invalid entry; please enter a whole number."
.ShowInput = True
.ShowError = True
End With
End If
End Sub