VB Range referencing

[Skebo]

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 1 Then ...

Is there a way to reference the range in some other way, like via its Excel name or by the value in the header row? It's a real pain to fix these macros everytime I move a column.

 

[Barrie Davidson]

On 2002-03-08 14:24, Skebo wrote:
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 1 Then ...

Is there a way to reference the range in some other way, like via its Excel name or by the value in the header row? It's a real pain to fix these macros everytime I move a column.

Sure,
If Target.Value = "Skebo" Then ...

Regards,

 

[Skebo]

I had already tried that. It returns a 'Run-time error: '13' type mismatch on the line If Target.Column = "Skebo" Then

Skebo is in both the name box and row1

 

[Barrie Davidson]

On 2002-03-08 14:34, Skebo wrote:
I had already tried that. It returns a 'Run-time error: '13' type mismatch on the line If Target.Column = "Skebo" Then

Skebo is in both the name box and row1

You've got the wrong argument there. You need
If Target.Value = "Skebo" Then..

 

[Skebo]

Just slap me next time.

Thanks that did it

 

[Russell Hauf]

Target.Value refers the value in the CELL, not a range name. If you want to refer to a named range, you can do something like this:

If Not Intersect(Target, Range("skebo")) Is Nothing Then
    ' Target (at least part of it) is in your skebo column
End If

Hope this helps,

Russell

 

[Skebo]

Oops, maybe I thanked you too soon :wink:

I have:
If Target.Value = "Skebo" Then ...

Instead of:
If Target.Column = 1 Then ...

I have:
Skebo in the name box and row1 for column 1(or A)

The If Then evaluates as false unless I put Skebo in column A. Target.Value = 'whatever is placed in column A' which does not = "Skebo"

My old code executed the Then code everytime I changed a value in column A.

 

[Barrie Davidson]

On 2002-03-08 14:59, Skebo wrote:
Oops, maybe I thanked you too soon /board/images/smiles/icon_wink.gif

I have:
If Target.Value = "Skebo" Then ...

Instead of:
If Target.Column = 1 Then ...

I have:
Skebo in the name box and row1 for column 1(or A)

The If Then evaluates as false unless I put Skebo in column A. Target.Value = 'whatever is placed in column A' which does not = "Skebo"

My old code executed the Then code everytime I changed a value in column A.

Alright, let's try

If Target.Column = Range("Skebo").Column Then...

Regards,

 

[Skebo]

Ummmm. That may have been bad crashed Excel.

 

[Skebo]

If Target.Column = Range("Skebo").Column Then

Seems to work fine. Thanks for your replies Barrie and Russell.