Try this. Hope this helps! If so, let me know, click "YES" below.

Option Explicit

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 Target.Column
Case 15 To 16
Range("Q" & Target.Row).Value = _
Sheets("tables").Range("B9").Offset(-Range("O" &
Target.Row), Range("P" & Target.Row))
Case 20 To 21
Range("V" & Target.Row).Value = _
Sheets("tables").Range("b9").Offset(-Range("T" &
Target.Row), Range("U" & Target.Row))
Case 26 To 27
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
--
Cheers,
Ryan


"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