Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range("fPeriod")) Is Nothing Then

If Target.Value = "WEEK" Then

Range("fPeriodType").Clear
With Range("fPeriodType").Validation

.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween, Formula1:="=lstWeekNums"
.IgnoreBlank = True
.InCellDropdown = True
End With
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Bhupinder Rayat" wrote in
message ...
Hi All,

I am trying to change the validation of one cell based on the value of
another cell.

I have to following code that basically states that if cell "fPeriod"
equals
"WEEK", then change the data validation of cell "fPeriodType" to a list
called "lstWeekNums".

Private Sub Worksheet_Change(ByVal Target As Range)

If Range("fPeriod") = "WEEK" Then

Range("fPeriodType").Clear
With Range("fPeriodType").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=
_
xlBetween, Formula1:=Range("lstWeekNums")
.IgnoreBlank = True
.InCellDropdown = True
End With

End If
End Sub

I get an "Method 'Range' of object'_Worksheet' failed error.

Can anyone help please?

Thanks,

B/