If cell contains "No", adjacent cells auto-populate "NA"

Hi all, I'm very new to VBA and would need your assistance on this which I've been trying to get it work but to no avail. I currently have a private sub for worksheet_change in my existing worksheet, and would now like to add a new criteria.

Currently, if "Any Funding?" column changes, both "Funding Approved?" and "Source of Funding" will be cleared of their contents. This is done with worksheet_change. Now, in the same "Any Funding" column, if selection is "No", I would like to set "Funding Approved?" and "Source of Funding" to "NA". Is this possible?


Current VBA is:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A2:A10")) Is Nothing Then
Target.Offset(0, 1).ClearContents
Target.Offset(0, 2).ClearContents
End If

End Sub

I've also attached a sample workbook for reference.
Thanks very much for anyone's help! Appreciate it!

Please Login or Register  to view this content.

In the future posts, please remember to put VBA codes in

Please Login or Register  to view this content.

[/NOPARSE] tags. For now, please edit your original post to include them, thanks!

CountLarge basically is just used to count the number of cells (in this scenario) in the Target variable. Meaning, the code will only work if you change 1 cell at a time. If you attempt to paste a list/multiple rows of values into column A, it will not work. If you want it to work for pasting a list of "No" it will require a different approach.