Change Event
 

Hi,

I am struggling to do something that I think should be easy, but I can't
figure it out.

I have a sheet within which I need to respond to specific changes:
If a cell in column H or column L is chnaged, I need to chnage the value in
column M of the same row.

I don't know which row will be altered, so I am trying to use a Worksheet
Change Event to trap the change with:

Private Sub Worksheet_Change(ByVal Target As Range)

Select Case True
Case Target.Column=8 or Target.Column=12
<update cell "M" in Target.Row
End Select
End Sub

What I can't figure out is the <update cell "M" in Target.Row code. I can
capture the Target.Row to a string but can't covert the string to a range to
update cell M Target.Row and I can't help thinking that there must be a
better way of doing things anyway.

This is in Office '07 under Vista FWIW

Any suggestions on how I achieve this would be gratefully received.

Hope this makes sense!

TIA

Dave

Change Event
 

Try the below. Right click the sheet tabView Code and paste the below code

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("H:H,L:L")) Is Nothing Then
Range("M" & Target.Row) = "Changed H/L"
End If
Application.EnableEvents = True
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Risky Dave" wrote:

Hi,

I am struggling to do something that I think should be easy, but I can't
figure it out.

I have a sheet within which I need to respond to specific changes:
If a cell in column H or column L is chnaged, I need to chnage the value in
column M of the same row.

I don't know which row will be altered, so I am trying to use a Worksheet
Change Event to trap the change with:

Private Sub Worksheet_Change(ByVal Target As Range)

Select Case True
Case Target.Column=8 or Target.Column=12
<update cell "M" in Target.Row
End Select
End Sub

What I can't figure out is the <update cell "M" in Target.Row code. I can
capture the Target.Row to a string but can't covert the string to a range to
update cell M Target.Row and I can't help thinking that there must be a
better way of doing things anyway.

This is in Office '07 under Vista FWIW

Any suggestions on how I achieve this would be gratefully received.

Hope this makes sense!

TIA

Dave

Change Event
 

Thanks

"Jacob Skaria" wrote:

Try the below. Right click the sheet tabView Code and paste the below code

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("H:H,L:L")) Is Nothing Then
Range("M" & Target.Row) = "Changed H/L"
End If
Application.EnableEvents = True
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Risky Dave" wrote:

Hi,

I am struggling to do something that I think should be easy, but I can't
figure it out.

I have a sheet within which I need to respond to specific changes:
If a cell in column H or column L is chnaged, I need to chnage the value in
column M of the same row.

I don't know which row will be altered, so I am trying to use a Worksheet
Change Event to trap the change with:

Private Sub Worksheet_Change(ByVal Target As Range)

Select Case True
Case Target.Column=8 or Target.Column=12
<update cell "M" in Target.Row
End Select
End Sub

What I can't figure out is the <update cell "M" in Target.Row code. I can
capture the Target.Row to a string but can't covert the string to a range to
update cell M Target.Row and I can't help thinking that there must be a
better way of doing things anyway.

This is in Office '07 under Vista FWIW

Any suggestions on how I achieve this would be gratefully received.

Hope this makes sense!

TIA

Dave