Trying to test several columns for changes when entering data
 

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

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