VBA - Worksheet - Change/SelectionChange: Not updating as expected

Question

Hello,

I have a sheet where I enter times (from and to) in columns H and I and columns J and K are supposed to show duration minutes and hours, respectively. Below is the code I came up with. I placed it in the Worksheet (not module) as I want the result to be volatile (instant). What it does is it updates every time I select a row of the updated cell. It also cleared contents of the header of the columns J and K. I keep it as SelectionChange. When I set it up as Change it freezes and I have to shut down excel.

How do I make it right so it returns as expected?

Thank you all in advance.

Alec.

**********************************************************

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim r, c

    Worksheets("NonWrench_Wrench Time").Select

    r = Selection.Row

    c = Selection.Column

    If Cells(r, 8) = "" Or Cells(r, 9) = "" Or Cells(r, 9) < Cells(r, 8) Then

    Cells(r, 10) = ""

    Cells(r, 11) = ""

    Else:

    Cells(r, 10) = (Cells(r, 9) - Cells(r, 8)) * 1440

    Cells(r, 11) = (Cells(r, 9) - Cells(r, 8)) * 1440 / 60

    End If

    On Error GoTo 0

End Sub

Answer 1

Alec,

Up to this point it was one row at a time. 

In my view you enlarged the scope.

Here you go...

Note:  rngCell is rngCell(1, 1)

          code searches thru Column 9 (I)

          rngCell(1, 0) is in column H

          rngCell(1, 3) is in column K

'2nd revision

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo BadEntry

  Dim rngCell As Excel.Range

  Dim rngAll As Excel.Range

  If Target(1).Column < 8 Or Target(1).Column > 9 Then

    Exit Sub

  Else

    Set rngAll = Application.Intersect(Target.Cells.EntireRow, Me.Columns("I"))

    For Each rngCell In rngAll.Cells

       If rngCell(1, 0).Value = vbNullString Or rngCell.Value = vbNullString Or _

          rngCell.Value < rngCell(1, 0).Value Then

          rngCell(1, 2).Value = vbNullString

          rngCell(1, 3).Value = vbNullString

       Else

          Application.EnableEvents = False

          rngCell(1, 2).Value = (rngCell.Value - rngCell(1, 0).Value) * 1440

          rngCell(1, 3).Value = (rngCell.Value - rngCell(1, 0).Value) * 1440 / 60

       End If

    Next 'rngcell

  End If

Application.EnableEvents = True

Exit Sub

BadEntry:

  VBA.MsgBox Err.Description & " - " & Err.Number

  Application.EnableEvents = True

End Sub

'---

Free Exce add-ins and workbooks at MediaFire...

http://www.mediafire.com/folder/lto3hbhyq0hcf/Documents

Nothing Left To Lose,

Works like a charm! Thank you for your effort!