apply cell change event to single column - WorksheetChange Event

[MiataDiablo]

dumbest question that will be posted all day, but here goes anyway.
How do I change the following macro so that it only applies to one
column instead of two? I don't know how to properly modify
Range(Cells(Target.Row, 15), Cells(Target.Row, 16)) to just one of
those columns. Deleting the second "range" gives me a run-time
error. Maybe I should be using something all together different
anyway. When the user enters a value into Column 14, I want the word
"Subfile" to appear in Column 15; however, if the user deletes the
text in Column 14, I want "Subfile" removed/deleted.

Any and all help is always appreciated.

Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False

If Target.Column = 14 And Target.Value <> "" Then
Range(Cells(Target.Row, 15), Cells(Target.Row, 16)).Value =
"Subfile"
End If

If Target.Column = 14 And Target.Value = "" Then
Range(Cells(Target.Row, 15), Cells(Target.Row, 16)).Value = ""
End If

Application.EnableEvents = True
End Sub

 

[Per Jessen]

dumbest question that will be posted all day, but here goes anyway.
How do I change the following macro so that it only applies to one
column instead of two?  I don't know how to properly modify
Range(Cells(Target.Row, 15), Cells(Target.Row, 16)) to just one of
those columns.  Deleting the second "range" gives me a run-time
error.  Maybe I should be using something all together different
anyway.  When the user enters a value into Column 14, I want the word
"Subfile" to appear in Column 15; however, if the user deletes the
text in Column 14, I want "Subfile" removed/deleted.

Any and all help is always appreciated.

Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False

If Target.Column = 14 And Target.Value <> "" Then
     Range(Cells(Target.Row, 15), Cells(Target.Row, 16)).Value =
"Subfile"
End If

If Target.Column = 14 And Target.Value = "" Then
 Range(Cells(Target.Row, 15), Cells(Target.Row, 16)).Value = ""
 End If

Application.EnableEvents = True
End Sub

Hi
Try this:

Range(Cells(Target.Row,15)).Value=""

Regards,
Per

 

[MiataDiablo]

Hi
Try this:

Range(Cells(Target.Row,15)).Value=""

Regards,
Per- Hide quoted text -

- Show quoted text -

fantastic! what did I ever do before Google??? One additional
question that I should have asked before. If 14 contains a comma or
an ampersand, then subfile should be subfiles.

 

[Gary''s Student]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Target
Set r = Range("N:N")
If Intersect(t, r) Is Nothing Then Exit Sub
Application.EnableEvents = False
v = t.Value
If v = "" Then
t.Offset(0, 1).Value = ""
Else
If v = "," Or v = "&" Then
t.Offset(0, 1).Value = "Subfiles"
Else
t.Offset(0, 1).Value = "Subfile"
End If
End If
Application.EnableEvents = True
End Sub

 

[MiataDiablo]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Target
Set r = Range("N:N")
If Intersect(t, r) Is Nothing Then Exit Sub
Application.EnableEvents = False
v = t.Value
If v = "" Then
    t.Offset(0, 1).Value = ""
Else
    If v = "," Or v = "&" Then
        t.Offset(0, 1).Value = "Subfiles"
    Else
        t.Offset(0, 1).Value = "Subfile"
    End If
End If
Application.EnableEvents = True
End Sub

--
Gary''s Student - gsnu2007h






- Show quoted text -

Gary's student, you're not only a great person, but you're officially
my hero today.

 

[MiataDiablo]

Gary's student, you're not only a great person, but you're officially
my hero today.- Hide quoted text -

- Show quoted text -

Well, I thought I could figure out the last part from here. What you
gave me works perfectly for subfiles if "N" contains ONLY a comma or
ONLY an ampersand. But the data for that cell will contain text like
"CLN1237, BNK1535 & GEA1981" or "CLN1237 & BNK1535". How do I insert
the "like" operator in your code the way I would use it for a query in
Access? I never thought I would ever say that making things happen in
Access is easier than Excel.

Thanks again for your help, I truly appreciate your time and
assistance.