Good Point, Dave!
--
Cheers,
Ryan


"Dave Peterson" wrote:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRngToInspect As Range

Set myRngToInspect = Me.Range("o:o,p:p,t:t,u:u,z:z,aa:aa")

'single cell at a time
If Target.Cells.Count 1 Then Exit Sub

If Application.Intersect(Target, myRngToInspect) Is Nothing Then
Exit Sub
End If

Application.EnableEvents = False

Select Case Target.Column
'you could use
'Case Is = 15, 16
'but I find that difficult to translate.
'I'd use:
Case Is = Me.Range("o1").Column, Me.Range("p1").Column
Me.Range("Q" & Target.Row).Value _
= Sheets("tables").Range("b9") _
.Offset(-Me.Range("o" & Target.Row).Value, _
Me.Range("p" & Target.Row).Value)

case is = ....

End Select

Application.EnableEvents = True

End Sub


I qualified your ranges (Me.Range()) and added some properties (.Value).
Risky Dave wrote:

Hi,

I am trying to use a worksheet Change Event to trap changes to specific
columns. depending on which column is chnaged by the user, the value in
another ciolumn will be altered; so, changes in either column O or P will
update column Q, changes in either column T or U will update column V and
changes in column Z or AA will update column AB.

What I can't figure out is the Select Case bit (where I've got the shouted
question below). Here's what I've put together, any comments would be much
appreciated.

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

If Not Application.Intersect(Target, Range("o:o,p:p,t:t,u:u,z:z,aa:aa")) Is
Nothing Then
Select Case 'WHAT GOES IN HERE?
Case Is = "o:o,p:p"
Range("q" & Target.Row).Value =
Sheets("tables").Range("b9").Offset(-Range("o" & Target.Row), Range("p" &
Target.Row))
Case Is = "t:t,u:u"
Range("v" & Target.Row).Value =
Sheets("tables").Range("b9").Offset(-Range("t" & Target.Row), Range("u" &
Target.Row))
Case Is = "z:z,aa:aa"
Range("ab" & Target.Row).Value =
Sheets("tables").Range("b9").Offset(-Range("z" & Target.Row), Range("aa" &
Target.Row))
End Select
End If
Application.EnableEvents = True
End Sub

TIA

Dave

--

Dave Peterson
.