Had to be something simple - ,my thanks

"Mike H" wrote:

Hi,

I never tested you statements but this should take care of the select case
bits

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 Is = 15, 16
Range("q" & Target.Row).Value =
Sheets("tables").Range("b9").Offset(-Range("o" & Target.Row), Range("p" &
Target.Row))
Case Is = 20, 21
Range("v" & Target.Row).Value =
Sheets("tables").Range("b9").Offset(-Range("t" & Target.Row), Range("u" &
Target.Row))
Case Is = 26, 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

Mike

"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