R
Risky Dave
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,p,t:t,u:u,z:z,aa:aa")) Is
Nothing Then
Select Case 'WHAT GOES IN HERE?
Case Is = "o,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
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,p,t:t,u:u,z:z,aa:aa")) Is
Nothing Then
Select Case 'WHAT GOES IN HERE?
Case Is = "o,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