VBA for Automation

Hello Joone,

The Intersect method returns a Range object that represents the rectangular intersection of two or more ranges. Target is the cell or range of cells the user has selected on the worksheet. If the result of the intersection is Nothing then the users selection was not in the Range of interest. If it is Not Nothing you need to use the Range object returned by the Intersect method, not Target.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim CellX As Range
  Const WS_RANGE As String = "C8:C24" '

    On Error GoTo ws_exit
    Application.EnableEvents = False

    Set CellX  =  Intersect(Target, Me.Range(WS_RANGE))
      If CellX Is Nothing Then Exit Sub    

    With CellX
      If IsNumeric(.Value) Then
         If IsNumeric(.Offset(0, -1).Value) Then
            .Offset(0, -1).Value = .Offset(0, -1).Value - .Value
            .Value = ""
         End If
      End If
    End With

ws_exit:
Application.EnableEvents = True
End Sub

Sincerely,
Leith Ross

Thanks for your reply Leith. I copied and pasted your code but it's not doing what I wanted it done. The code that I posted was working but only for column C. I needed to edit the code to include column D.

Thanks again.

Hello Joone,

The code works for me and I am using Windows Xp with Excel 2000. You don't say specify what "D" is to be or how the value is calculated. Post back with that info and I'll add it to the code.

Sincerely,
Leith Ross