How to set multiple columns in "If Target.Column ="
 

I want to run the same code when one of several columns contain the active
cell. How do I do this without repeating the code.
When I used

If Target.Column = 7 Or 8 Or 9 Or 10 Or 11 Or 12 Or 13 Then

the code is executed when any cell in the sheet is selected not just in the
above columns.
I tried using an array without luck either.
Also, I am not sure yet if all of the columns will be sequential or not.
Thanks in advance for any assistance.
Brad

Answer: How to set multiple columns in "If Target.Column ="
 

Hi Brad,

To set multiple columns in "If Target.Column =", you can use the Select Case statement. This will allow you to run the same code when one of several columns contains the active cell without repeating the code.

Here's an example code:

1. Formula:

   ---

   Private Sub Worksheet_SelectionChange(ByVal Target As Range) 


   ---

2. Formula:

   ---

   Select Case Target.Column 


   ---

   1. Case 7, 8, 9, 10, 11, 12, 13
      'Your code goes here
   2. Case Else
      'Do nothing
3. Formula:

   ---

   End Select 


   ---

4. Formula:

   ---

   End Sub 


   ---

In this code, the Select Case statement checks if the active cell is in columns 7 to 13. If it is, your code will be executed. If not, nothing will happen.

You can add your own code in the "Your code goes here" section. This will be executed when the active cell is in one of the specified columns.

How to set multiple columns in "If Target.Column ="
 

Brad

post the rest of your code

Regards

Trevor


"Brad K." wrote in message
...
I want to run the same code when one of several columns contain the active
cell. How do I do this without repeating the code.
When I used

If Target.Column = 7 Or 8 Or 9 Or 10 Or 11 Or 12 Or 13 Then

the code is executed when any cell in the sheet is selected not just in
the
above columns.
I tried using an array without luck either.
Also, I am not sure yet if all of the columns will be sequential or not.
Thanks in advance for any assistance.
Brad

How to set multiple columns in "If Target.Column ="
 

Brad,

You can use the Intersect() function

Private Sub Worksheet_Change(ByVal Target As Range)
'if using the change event
If Not Intersect(Target, Range("G:M")) Is Nothing Then _
MsgBox MyCell.Address
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'if using the selection change event
If Not Intersect(ActiveCell, Range("G:M")) Is Nothing Then _
MsgBox MyCell.Address
End Sub

Regards,
KL


"Brad K." wrote in message
...
I want to run the same code when one of several columns contain the active
cell. How do I do this without repeating the code.
When I used

If Target.Column = 7 Or 8 Or 9 Or 10 Or 11 Or 12 Or 13 Then

the code is executed when any cell in the sheet is selected not just in
the
above columns.
I tried using an array without luck either.
Also, I am not sure yet if all of the columns will be sequential or not.
Thanks in advance for any assistance.
Brad

How to set multiple columns in "If Target.Column ="
 

const START_COL as integer=7
const END_COL as integer=13

If Target.Column = START_COL and Target.Column <= END_COL Then
'do stuff
end if


Tim




"Brad K." wrote in message
...
I want to run the same code when one of several columns contain the
active
cell. How do I do this without repeating the code.
When I used

If Target.Column = 7 Or 8 Or 9 Or 10 Or 11 Or 12 Or 13 Then

the code is executed when any cell in the sheet is selected not just
in the
above columns.
I tried using an array without luck either.
Also, I am not sure yet if all of the columns will be sequential or
not.
Thanks in advance for any assistance.
Brad