Run code when active cell is any cell in column 2

[HobbitK]

Hi everyone ...
Another (probably) simple Excel VBA Newbie question.
I need to run a Sub whenever the active cell is any cell in a specific column. I can get it to work for a specific cell by using
If Target.Address = "$A$2" Then
Do Something

But If I try it using any variable on this
If Target.address = "G:G" then
Do Something

I get either a type mismatch error, a With Block not set error, or it just skips right past the code being called.
I am using the Worksheet_SelectionChange event.
Thanks
Michael

 

[combo]

Hi Michael,
You can use SelectionChange only as a trigger:
[tt]Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If ActiveCell.Column = 7 Then
' Do something
End If
End Sub[/tt]

combo

 

[Loomah]

Hi Michael

To take this a stage further you could look at "Intersect"

By way of example

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("Frodo")) Is Nothing Then
MsgBox Target.Address
'=======================
'do proper stuff
'=======================
End If
End Sub

This will perform taksks if you select a cell within the (tiresomely/wittily) named range "Frodo"

;-) If a man says something and there are no women there to hear him, is he still wrong?

 

[Loomah]

Just learned a little more myself!

This will perform the task(s) if you select a cell where columns D or E cross Frodo

If Not Intersect(Target, Columns("D:E"), Range("frodo")) Is Nothing

;-) If a man says something and there are no women there to hear him, is he still wrong?

 

[HobbitK]

Thanks Guys ...
IT Works

Michael