apply cell change event to single column - WorksheetChange Event
 

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

apply cell change event to single column - WorksheetChange Event
 

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
If t.Value = "" Then
t.Offset(0, 1).Value = ""
Else
t.Offset(0, 1).Value = "Subfile"
End If
Application.EnableEvents = True
End Sub
--
Gary''s Student - gsnu2007h


" wrote:

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

apply cell change event to single column - WorksheetChange Event
 

On 3 Maj, 11:52, wrote:
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

apply cell change event to single column - WorksheetChange Event
 

On May 3, 6:41*am, Per Jessen wrote:
On 3 Maj, 11:52, wrote:





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- 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.

apply cell change event to single column - WorksheetChange Eve
 

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


" wrote:

On May 3, 6:41 am, Per Jessen wrote:
On 3 Maj, 11:52, wrote:





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- 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.

apply cell change event to single column - WorksheetChange Eve
 

On May 3, 8:39*am, Gary''s Student
wrote:
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



" wrote:
On May 3, 6:41 am, Per Jessen wrote:
On 3 Maj, 11:52, wrote:

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- 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.- Hide quoted text -

- Show quoted text -

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

apply cell change event to single column - WorksheetChange Eve
 

On May 3, 1:52*pm, wrote:
On May 3, 8:39*am, Gary''s Student





wrote:
Try this:

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

--
Gary''s Student - gsnu2007h

" wrote:
On May 3, 6:41 am, Per Jessen wrote:
On 3 Maj, 11:52, wrote:

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,ifthe 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

IfTarget.Column = 14 And Target.Value < "" Then
* * *Range(Cells(Target.Row, 15), Cells(Target.Row, 16)).Value =
"Subfile"
EndIf

IfTarget.Column = 14 And Target.Value = "" Then
*Range(Cells(Target.Row, 15), Cells(Target.Row, 16)).Value = ""
*EndIf

Application.EnableEvents = True
End Sub

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. *If14containsacommaor
an ampersand, then subfile should be subfiles.- Hide quoted text -

- Show quoted text -

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.