Hi
Do you need to do this in code? Debra Dalgliesh shows how to do this without
code at:
http://www.contextures.com/xlDataVal13.html

Hope this helps, I have never tried to code this myself.
Peter

"paapa21" wrote:

Hi all, I'm wondering if someone can help me out here. I'm trying to automate
an excel sheet whereby a selection of a value form a drop down list will
present a differnt dropdown in adifferent cell. However my code is throwing
Run-time error 1004. Below is the code and the debug point to .Ignoreblank =
true

Dim CommentRed As String
Dim CommentAmber As String
Dim CommentGreen As String

CommentRed = "OTC, WIP in course, Credit in course, Credit Submitted"
CommentAmber = "Credit Approved, Awaiting Confirmation, In documentation"
CommentGreen = "In documentation, Deal Completed"


If Target.Column = 4 Then
ThisRow = Target.Row
With Range("E" & ThisRow).Validation
.Delete
Select Case Range("M" & ThisRow).Value
Case "WIP Red"
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:=xlEqual, Formula1:=CommentRed
Case "WIP Amber"
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:=xlEqual, Formula1:=CommentAmber
Case "WIP Green"
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:=xlEqual, Formula1:=CommentGreen
End Select
.IgnoreBlank = True
.InCellDropdown = True
End With
End If

Can some one please help me out. I'm behind deadline