Visual Basic Help - Need N/A or Drop Down List to Populate - Based on Data in Another Cell

[lomo0208]

I posted this question on another group and was able to get an answer, however I need one item changed and I have not been able to get assistance on this change. I am hoping someone here can help me out with this question!

Original question:
Cell B8 can be blank, Yes or No.
When B8 is Yes, I need my data validation list to appear in fields G28:G35. This is simple and I have my listed named Status.
When B9 is No, I need N/A to automatically populate in fields G28:G35. I also need these cells locked.
I found some VBA help topics but I cannot get it to work for me.

I am not sure if I need to add my data validation first or second or what to key in Visual Basic. I will be repeating this same scenario at least 15 times with different cells.

Original Response:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$B$8" Then
        Select Case Target
            Case "Yes"
                Range("G28:G35").Select
                Range("G28:G35").Locked = False
                With Selection.Validation
                    .Delete
                    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                        xlBetween, Formula1:="=IF($B$8=""Yes"",INDIRECT(""Status""))"
                    .IgnoreBlank = True
                    .InCellDropdown = True
                    .InputTitle = ""
                    .ErrorTitle = ""
                    .InputMessage = ""
                    .ErrorMessage = ""
                    .ShowInput = True
                    .ShowError = True
                 End With
            Case "No"
                Range("G28:G35").Locked = True
                For Each cell In Range("G28:G35")
                    cell.Value = "N/A"
                Next cell
            Case Empty
                Range("G28:G35").Locked = False
                For Each cell In Range("G28:G35")
                    cell.ClearContents
                Next cell
        End Select
    End If
End Sub

New Assistance Needed:
I decided to move cell B8 to another sheet because I am going to have a large number of yes/no questions. The cell is now on a sheet called Account Summary. I tried to update the code and reference this sheet, but apparently I do not know what I am doing. I really need this to work. I have researched and tried so many different ways to reference the other tab and I just can't figure it out.

If someone incorrectly enters "No" in B8 and deletes "No", the N/As in the range will disappear. If they change "No" to "Yes" in B8, the N/As remain in the range. Is it possible to update the code to indicate if "Yes" is selected, the cells will clear and only have the option of the drop down list? This is optional and I can live without this being done.

Your help is greatly appreciated!

[lomo0208]

Thank you for the quick response and feedback. The original post has been updated.

[Fotis1991]

Thread closed as per OP request, as will post in Comercial fORUM.