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

Try the below. Right click the sheet tab>View 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
>
>

Thanks

"Jacob Skaria" wrote:

> Try the below. Right click the sheet tab>View 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
> >
> >