Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validation with if
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Validation Data using Validation Table cell range..... | Excel Discussion (Misc queries) | |||
Data Validation Update Validation Selection | Excel Worksheet Functions | |||
data validation invalid in dynamic validation list | Excel Discussion (Misc queries) | |||
data validation invalid in dynamic validation list | Excel Worksheet Functions | |||
Data validation with validation lists and combo boxs | Excel Discussion (Misc queries) |