Conditonal DropDown with IF condition

Hi Experts,

This is my first post and before posting, I have searched the whole forum for this issue.

The issue is as follows:

Suppose A2 cell has value as "Yes" or "yes".
So if A2 = "Yes" then
B2 will have a drop down list of "Yes" and "No", so that user can select yes or no from the Drop-down list.
But if the A2 cell has any values other than Yes/yes then
B2 will show "N/A"

Current workaround is:

B2=IF(OR(A2="Yes",A2="yes"),"Yes","N/A")

Please help regarding the issue.

Regards,
Sap.

hi, saps19, VB option, try to change A2 cell value

grey out added, please check attachment, please specify what should be protected and when?

Originally Posted by watersev

grey out added, please check attachment, please specify what should be protected and when?

Thanks Water... I have already modified the code, please check below.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$2" Then
    
    Range("a2").BorderAround LineStyle:=xlContinuous
    Application.EnableEvents = 0

    If LCase(Target) = "yes" Then

        Range("b2").Delete
        Range("b2").Validation.Add Type:=xlValidateList, Formula1:="Yes, No"
        Range("b2").BorderAround LineStyle:=xlContinuous

    Else

        With Range("b2")

            .Validation.Delete
            .Value = "N/A"
            .Interior.ColorIndex = 40
            .BorderAround LineStyle:=xlContinuous

        End With

    End If

    Application.EnableEvents = 1

End If

End Sub

But this code works for only one cell. I want to run this macro in all the cells.

Please find the attached excel sheet for details.
According to "Worked?" option, the "Availed?" option will change.

OUTPUT should be like:
If cell a1/2/3/4/5/../n equal to Yes 
then b1/2/3/4/5/../n will have a dropdown of "Yes" and "No"
else b1/2/3/4/5/../n will be disabled/ "N/A"

please check attachment

Originally Posted by watersev

please check attachment

Thanks for the help water... Now I'm able to run macro in alternate cells(Like A1, A4, A7 etc). Please find the below code and suggest if it needs any further modification / improvement.

Private Sub Worksheet_Change(ByVal Target As Range)

For i = 1 To 10 Step 3
    If Not Intersect(Target, Columns(i)) Is Nothing Then
        For j = 1 To 10 Step 1
            For Each cl In Target
                If cl.Column = i Then
        
                    Application.EnableEvents = 0

                    If LCase(cl) = "yes" Then
       
                        cl.Offset(, 1).Clear
                        cl.Offset(, 1).Validation.Add Type:=xlValidateList, Formula1:="Yes, No"
                        cl.Offset(, 1).BorderAround LineStyle:=xlContinuous
                       
                    ElseIf cl <> "" Then
        
                        With cl.Offset(, 1)
                            .Clear
                            .Value = "N/A"
                            .Interior.ColorIndex = 40
                            .BorderAround LineStyle:=xlContinuous
                        End With
            
                    ElseIf cl = "" Then
            
                        cl.Offset(, 1).Clear
    
                    End If
    
                    Application.EnableEvents = 1
        
                End If
                If j = 10 Then
                    Exit For
                End If
            Next
        Next j
    End If
Next i
End Sub

However, I need to achieve the following two points:
1. I don't want to run this macro after 10th row.
2. The macro should start from my desired location(i.e: Suppose from B5 or C4 etc.).

Please help me with this two point.

Cheers,
Sap.

please check attachment, the code will work in any column if target (changed cell) is within 1:10 rows including

Originally Posted by watersev

please check attachment, the code will work in any column if target (changed cell) is within 1:10 rows including

Thanks water... I tried to modify the codes. I have attached the excel sheet with some points.

I want to run the macro in "Worked?" column only; the values in "Last date" and "Used date" will vary depend on the value of "Availed"(Yes/No) column. There will be a date calculation like this: =DATE(YEAR(C1),MONTH(C1), DAY(C1)+90) in "Last date" column. "Used date" column will have manual entry if "Availed" column has the value as "Yes", otherwise "Used Date" column will contain "N/A".

Please help me with it accordingly.

Cheers,
Sap.