Validation with if
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
"Spade" wrote in message
...
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.
|