Set Target.Address to a Column

[Salrandin]

Hi All,

I am new here and from my recent lurkings have found my pathetic vision of excel to be shattered... I did not know you could do so much in it. Anyways I am looking to learn and my first question:

Is there a clean way to set target.address to a column?

If Target.Address = "$A$1" Then

I have googled some ways to set it to a defined range but I want a whole column. Any help appreciated. Thanks.

 

[VoG]

What are you trying to do? Maybe

If Target.Column = 1 Then

 

[Jonmo1]

Welcome to the board...

You should also look into using Intersect...

If Not Intersect(Target, Range("A:A")) Is Nothing Then
    'Do some stuff
End if

This will "Do some stuff" only if Target Intersects with Column A


Hope that helps.

 

[Salrandin]

What are you trying to do? Maybe

If Target.Column = 1 Then

Ahh... my google sources made it more complicated then it seemed using intersects and the such.. boy do i feel foolish...

Thanks.

 

[Salrandin]

Welcome to the board...

You should also look into using Intersect...

If Not Intersect(Target, Range("A:A")) Is Nothing Then
    'Do some stuff
End if

This will "Do some stuff" only if Target Intersects with Column A


Hope that helps.

I see... I know how to do all the other stuff I just needed help with the range. Thanks!

 

[Salrandin]

Oops another question, how do I set it to the Column 1 of Worksheet 1 if this code is on another sheet? Thanks.

 

[VoG]

Oops another question, how do I set it to the Column 1 of Worksheet 1 if this code is on another sheet? Thanks.

That is confusing. Please post your complete code and what you expect it to do.

 

[Salrandin]

That is confusing. Please post your complete code and what you expect it to do.

If Target.Column = Sheets("Open").Column(1) Then
 
    'do stuff (havnt done this piece yet but its easy)
 
end if

I like to have all my code on a separate sheet from the data and this current code is on a worksheet named "code" so I need it to see if an event occurs in Column A of sheet "Open" which will then trigger my macro. Thanks.

 

[Jonmo1]

Oops another question, how do I set it to the Column 1 of Worksheet 1 if this code is on another sheet? Thanks.

You don't...

You would need to repeat the code in each individual sheet's module.

Or utilize the Workbook_SheetChange event, which goes in the ThisWorkbook Module.

Then you test the name of the sheet that was changed...

If sh.Name = "Sheet1" Then
    If Not Intersect(Target, sh.Range("A:A")) Is Nothing Then
        'Do some stuff
    End If
End If

 

[Salrandin]

You don't...

You would need to repeat the code in each individual sheet's module.

Or utilize the Workbook_SheetChange event, which goes in the ThisWorkbook Module.

Then you test the name of the sheet that was changed...

If sh.Name = "Sheet1" Then
    If Not Intersect(Target, sh.Range("A:A")) Is Nothing Then
        'Do some stuff
    End If
End If

What is sh? Am i suppose to declare it as sheeet1? Sorry I am not very good at this.