VBA Help: If Any Text In A Row, Default Column A To "N"

[milwphil]

Good morning!

I inherited a workbook that cleans data for uploading into another application. I noticed there was an opportunity to automate one of the entries, but I'm having difficulty finding the right code.

In short, this is what I'd like to do. If someone enters or "cuts and pastes" data in a row, the corresponding row and column A will default to "N".

For Example: A user will enter or cut and paste data in row 2 column B,C,D, E, F, G, H, I and or J. If there is any data in any of the cells of that row, I'd like column A row 2 to default to "N". Likewise, for any other rows that have data.

I thought it may be possible to include it in a Worksheet_Change that already exists (removing hyphens, extra spaces), but everytime I add something it doesn't seem to work or nothing happens.

Any suggestions? Here is my current code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oCell As Range
'removes leading and trailing spaces
'Application.EnableEvents must be turned off to remove leading and trailing spaces and then turned on at the end again.
Application.EnableEvents = False
For Each oCell In Target
    oCell.Value = WorksheetFunction.Trim(oCell.Value)
Next oCell
'-------------------------------------------
'removes hyphen and extra spaces in the licenses column
Columns("I").Replace What:="-", Replacement:="", _
LookAt:=xlPart, SearchFormat:=False, ReplaceFormat:=False
Columns("I").Replace What:=" ", Replacement:="", _
LookAt:=xlPart, SearchFormat:=False, ReplaceFormat:=False
'-----------------------------------------------------
Columns("I").Replace What:="-", Replacement:="", _
LookAt:=xlPart, SearchFormat:=False, ReplaceFormat:=False
 If Target.Cells.Count = 1 Then
  Constants.DisableWorkbook
  If Target.Row > 1 Then
   Select Case Target.Column
    Case 1
     Validations.OrderMVRValidation Target
   End Select
  End If
  Constants.EnableWorkbook
 ElseIf Target.Cells.Count > 1 Then
  If Operations.CheckUndoStack("Paste") Then
   Constants.DisableWorkbook
   Constants.DisableScreen
 
 
   Application.Undo
   Target.PasteSpecial xlPasteValues, xlPasteSpecialOperationNone, False, False
 
 
   For Each oCell In Target
    oCell.Value = Validations.CleanNonStndChars(CStr(oCell.Value))
   Next oCell
 
   Constants.EnableScreen
   Constants.EnableWorkbook
  End If
  If Target.Cells.Locked Then
   Target.Locked = False
  End If
 End If
 Application.EnableEvents = True
End Sub

Thank you in advance for your help!

Phil

 

[venkat1926]

I am not sure I have understood your correctly
let me rephrase

if target row is not 2 do not do anything except making A2 blank
if target row is 2 but target column <column B or >column J do not do anything except making A2 blank
if any entry is freshly made in row 2 between column B and J then enter "N" in A2

if this is so try the following event code. right click the sheet tab and click view code and there copy paste this code and save the file

now make any entry in any cell depending upon above conditions A2 will have N. do some experimental entries and check whether the event code does what you want. If necessary modify the event code.

Private Sub Worksheet_Change(ByVal Target As Range)
Range("A2") = ""
If Target.Row <> 2 Then Exit Sub
If Target.Column < 2 Or Target.Column > 10 Then Exit Sub
Application.EnableEvents = False
If Target <> "" Then Range("A2") = "N"
Application.EnableEvents = True
End Sub

 

[milwphil]

Thanks for the repsonse and I apologize for the delay getting back to you!

I think this is going down the right track, but I need it to do the same for all rows.

If any data in a row, the relative row(cell) in column A will default to "N".

For example, if someone adds data in J2, C5,D9.... In A2 there would be an "N", A5 "N" and D9 "N".

Thanks for all of your help!
Phil