Data Validation

[irfman]

I have entered a data validiation condition in cell A1 with options "Yes" and "No". In cell B2 i want the outcome as follows:

• On selecting "Yes" option in A1, the value in cell B2 is automatically changed to 100%
• On selecting "No" option in A1, no change takes place in cell B2 and the user is free to enter any value manually.

Is this possible? Appreciate any assistance in the matter.

Regards

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Cell B1 must be formatted as a percentage.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
    If Target = "Yes" Then
        Range("B" & Target.Row) = 1
    End If
End Sub

 

[irfman]

thanks a ton. really appreciate it. Is there any way if this can be done without using macros?

 

[JLGWhiz]

thanks a ton. really appreciate it. Is there any way if this can be done without using macros?

Not if you allow the user to make manual entries into the destination cell.

 

[mumps]

You are very welcome. @JLGWhiz has answered your question. If you don't allow manual entry, then this formula in B1 should work: =IF(A1="Yes",1,"")

 

[irfman]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Cell B1 must be formatted as a percentage.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
    If Target = "Yes" Then
        Range("B" & Target.Row) = 1
    End If
End Sub

what will be the change in VB code if cell A1 is located in work Sheet 1 and cell b2 is located in work sheet2.

thanks in anticipation.

 

[mumps]

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
    If Target = "Yes" Then
        Sheets("Sheet2").Range("B2") = 1
    End If
End Sub