Clear Contents of cell but still be able fo freeform edit the cell

[KakarikoHero]

I want to be able to have a value in the first cell to be "yes", "no", or "NA". Each column is a checklist for auditing. If "NA" is entered into column W the next X:AE cells to return "NA" because we can skip the row. I got that part easy peasy.

If cell W contains "No" I want cells Y:AA to be "NA" since those check points can't be audited. But i still want to edit the rest of the cells but cannot because of ".clearcontents"

Also, if Cell W contains "Yes" I want to clear all X:AE so that I can edit them as Yes or No.

Finally, if X is No then I want AB:AC to be "NA".

I was able to do this all with an IF function but wanted to step it up with VBA since once the cell is overwritten I lose the formula. So what can I use to still be able to edit these. Here's what I got. Please forgive how crude it is I was never trained in VBA.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim LastRow As Long
LastRow = Range("W" & Rows.Count).End(x1Up).Row
Dim i As Long
For i = 2 To LastRow
If Range("W" & i).Value = "NA" Then
Range("X" & i).Value = "NA"
Range("Y" & i).Value = "NA"
Range("Z" & i).Value = "NA"
Range("AA" & i).Value = "NA"
Range("AB" & i).Value = "NA"
Range("AC" & i).Value = "NA"
Range("AD" & i).Value = "NA"
Range("AE" & i).Value = "Account Closed"
ElseIf Range("W" & i).Value = "No" Then
Range("X" & i).ClearContents
Range("Y" & i).Value = "NA"
Range("Z" & i).Value = "NA"
Range("AA" & i).Value = "NA"
Range("AB" & i).ClearContents
Range("AC" & i).ClearContents
Range("AD" & i).ClearContents
Range("AE" & i).ClearContents
ElseIf Range("W" & i).Value = "Yes" Then
Range("X" & i).ClearContents
Range("Y" & i).ClearContents
Range("Z" & i).ClearContents
Range("AA" & i).ClearContents
Range("AB" & i).ClearContents
Range("AC" & i).ClearContents
Range("AD" & i).ClearContents
Range("AE" & i).ClearContents
End If
If Range ("X" & i).Value = "No" Then
Range("AB" & i).Value = "NA"
Range("AC" & i).Value = "NA"
ElseIf Range("X" & i).Value = "Yes" Then
Range("AB" & i).ClearContents
Range("AC" & i).ClearContents
End If
Next I
End Sub

Also, If anyone has suggestions for cleaning this up It would be greatly appreciated. Right now it's so clunky it causes Excel to lag

Thanks in advance!

 

[DanteAmor]

Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.

In order for me to better understand the problem, I would like to ask a few questions:

1. Could you explain with an example what you mean by the following:

But i still want to edit the rest of the cells but cannot because of ".clearcontents"

2. Is it possible that the value of cell X will never have the value "Y" or "N" since in your previous lines you can change it to "NA" or clear it?
3. You have the code execution in the Selection event, that means it will be executed when you select any cell. Is that correct or should it be run when you select certain cells or maybe run when you modify certain cells?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

4. If you don't want execution when you select a cell, then it should be a macro in a module and maybe run it on button press when you need to?
I hope to hear from you soon.
Respectfully
Dante Amor

 

[KakarikoHero]

Dante, Thank you for such a quick reply!

1. So I have the .ClearContents in case the associate enters the wrong data and needs to erase it. NA in column W means the account is closed so It auto-fills NA across the row. My thought was that if they accidentally entered NA they could change it and it would clear the autofilled cells.

2. So here's how it is supposed to work:
W = Is doc 1 of 2 on file Y/N? If Y then Y,Z,&AA = Y or N.
If N then Y,Z,&AA = NA 'because doc was not on file to audit.
X = Is doc 2 of 2 on file Y/N?
If Y Then AB&AC need a Y or N.
If N then AB&AC = NA 'because doc was not on file to audit.
(AD&AE are just free-form comment cells).

3. Preferably run when cell in column W changes. I tried this without selection but I needed to run it every time (I am google taught so any Sub suggestions would help).

4. I wish I could use a button. I love buttons (which you can probaby tell by my vba). The problem is these files come in with up to 300 rows of data we're auditing. Each row being a different account and that would require a button or 3 for every row... right?

 

[KakarikoHero]

I figured it out. I just used the Is Nothing if statement. That did the trick. It was one of those situations where I had to give up and stop thinking about it to figure it out.