Trying to test several columns for changes when entering data
I would guess that
Cells(rngTarget.Column, 2).Value
should be
Cells(2,rngTarget.Column).Value
and for the first IF statement
If Intersect(Range("A:A, C:C, F:F, BD:bd"), rngTarget) Then
should be
If Not Intersect(Range("A:A, C:C, F:F, BD:bd"), rngTarget) is nothing Then
--
Regards,
Tom Ogilvy
"Bruce" wrote:
I have tried the code below, but it does not seem to work. Some times I get
a type mismatch on the first "IF" statement, then other times it will show
the first two msgbox.
What I am trying to do is to test for different column changes, and then
based on what column, run an event.
example. For column A, I want to copy code from another WS,
Column BD, the set the formating of the cell after a PASTE
There are about 6 columns I need to check.
Thanks
Bruce
Private Sub worksheet_change(ByVal rngTarget As Range)
If Intersect(Range("A:A, C:C, F:F, BD:bd"), rngTarget) Then
Application.EnableEvents = False
irow = ActiveCell.Row
temp = MsgBox(irow)
icolumn = ActiveCell.Column
temp = MsgBox(icolumn)
Select Case Cells(rngTarget.Column, 2).Value
Case 1
MsgBox "columnA"
Case 3
MsgBox "test"
Case 6
MsgBox "testf"
Case 57
MsgBox "Column BD"
Case Else
End Select
Else
'do nothing - the cell we need to watch hasn't been changed
End If
Application.EnableEvents = True
End Sub
|