Defining Active Cell Location

[theteerex]

In the snippet of code below, what would I have to do to change the way I reference the active cell?

Private Sub Worksheet_Change(ByVal Target As Range) '_Change  creates the variable as a target, transferred as a value to "ProjectName"
    Dim ans As Integer


[B][COLOR=#ff0000]    If Target.Column = 2 And Target.Row > 1 And Target.Value = "" Then [/COLOR][/B]

How can I change the red to this?

[COLOR=#008000]    If Worksheets("Projects").Range("Records[OPPORTUNITY]") And Worksheets("Projects").Target.Row > 1 And Target.Value = "" Then[/COLOR]

 

[Peter_SSs]

A few questions about your code:

• Do I still need the

  ByVal Target As Range

  ?

If there is no Target, isn't this useless?

Yes you do. There is still a Target, it just doesn't have a 'Value' if there are multiple cells in it. Target is still used in the code

Set Changed = Intersect(Target, Columns(2), Rows("2:" & Rows.Count))

• Is there some way this sub could have done the same thing without the

  _Change

  ?

Yes, but then the code wouldn't automatically fire as soon as you deleted something from column B. You would have to run the macro yourself somehow,

 

[theteerex]

Peter, I am getting an error due to the undo line in your revised code.

Run-time error 1004
Method 'Undo of object' _Application Failed

 

[Peter_SSs]

Peter, I am getting an error due to the undo line in your revised code.

Run-time error 1004
Method 'Undo of object' _Application Failed

The code is working for me & I have been unable to reproduce that error in my workbook.

Are you using the code exactly as from post 9 or have you modified it or combined it with other code?

Can you describe exactly what you did in your worksheet that triggered the code to produce the error?