Validation with if

[Spade]

I need to create a formula wich contains an if clause and a validaiton (list).

A1= will be "member" or "non member" (will be selcted from a list: ok)
A2= 0.30 is A1="non member" and 1.0 A1="member" (if clause: ok)
A3= amount will be entered (manually)
A4= if A2="non member" then will ask for "Yes" or "No" (as validation) and
if A2="member" will return as "Recorded"

Thanks for your help.

 

[Sandy]

Hi Spade

the following code should do the job :-

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo 0

Application.EnableEvents = False
Application.ScreenUpdating = False

If Not Intersect(ActiveCell, Range("A1:IV1")) Is Nothing Then

If ActiveCell.Value = "Member" Then
With ActiveCell.Offset(3)
.Validation.Delete
.Value = "Recorded"
End With

ElseIf ActiveCell.Value = "Non-Member" Then
With ActiveCell.Offset(3)
.Value = ""
.Validation.Delete
.Validation.Add xlValidateList, Formula1:="Yes, No"
.Validation.IgnoreBlank = True
.Validation.InCellDropdown = True
.Interior.ColorIndex = 6
.Borders.LineStyle = xlContinuous
.Borders.Weight = xlThin
.Borders.ColorIndex = 3
End With

Else
With ActiveCell.Offset(3)
.Validation.Delete
.Value = ""
End With

End If

Else
ActiveCell.Interior.ColorIndex = xlNone
ActiveCell.Borders.LineStyle = xlNone
End If

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

I have taken the liberty of highlighting cells that require input and then
removing the highlighting when entry has been made.

hth
Sandy