CODEWITHSUNDEEP
excelvba
Windmill
Windmill

Reputation: 163

How to use Intersect with a Table Range

I'm needing to modify my code below to use a table range rather than a cell range. The code below allows the selected cells between the given range to change from 9 to 0 and back again if selected. However I need this to work with a Table so for example Table1 Column1 is my range rather than G5 to G1604. I would like it so that when I specify Column1 for it to get the whole of that column as the range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

 If Not Intersect(Target, Range("G5:G1604")) Is Nothing Then
  If Target.Value = 9 Then
        Target.Value = 0
    Else
    Target.Value = 9
  End If
 End If
End Sub

Upvotes: 1

Views: 9023

Answers (1)

Scott Holtzman
Scott Holtzman

Reputation: 27249

See this great tutorial on Excel VBA for Tables

Your code will look like this (adjust Table name and column reference accordingly): 

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

 If Not Intersect(Target, Me.ListObjects("Table1").ListColumns(1).DataBodyRange)) Is Nothing Then
  If Target.Value = 9 Then 
        Target.Value = 0
    Else
    Target.Value = 9
  End If
 End If

End Sub

As a side note: Are you sure you want this code to fire everytime you move from one cell to another on the worksheet? Or do you want it to fire everytime you make a change to a cell in the column of the table? If the latter, change to Worksheet_Change event.

Upvotes: 4

Related Questions