Worksheet_Change help please

[Beans]

My spreadsheet is tracking budget vs acutals for various stages in a project.
Column E stores the Project stage, Initiation, Planning and Execution.
Columns H through J store original budget, revised budget and actual for the
Initiation Stage, Columns K through M store original budget, revised budget
and actual for the Planning stage, Columns N through P store original budget,
revised budget and actual for the Execution Stage, Column Q remaining budget
and the grand total is stored in Column S.

Depending on what is selected in Column E, the total (column S) is updated.

This is the code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range

Set rng = Intersect(Target, Range("E:E"))

If Not rng Is Nothing Then
On Error GoTo XIT
Application.EnableEvents = False
With rng
Select Case (.Value)
Case "Initiation":
'if there is a value in the revised budget column (4) use it
and add any remaining budget column (12) to the total
'otherwise use the original budget column (3) + any
remaining budget column (12)
If .Offset(0, 4).Value > 0 Then
.Offset(0, 13).Value = .Offset(0, 4).Value + .Offset(0,
12).Value
Else
.Offset(0, 13).Value = .Offset(0, 3).Value + .Offset(0,
12).Value
End If
Case "Planning":
'if there is a value in the revised budget for planning
column (7) use it as well as the actual cost
'for the previous stage column (5) Plus any remaining
budgetcolumn (12) in the total column (13)
If .Offset(0, 7).Value > 0 Then
.Offset(0, 13).Value = .Offset(0, 5).Value + .Offset(0,
7).Value + .Offset(0, 12).Value
Else
.Offset(0, 13).Value = .Offset(0, 5).Value + .Offset(0,
6).Value + .Offset(0, 12).Value
End If
Case "Execution":
'if there is a value in the revised budget for execution
column use it as well as the actual cost
'for the previous stage(s) in the total
If .Offset(0, 10).Value > 0 Then
.Offset(0, 13).Value = .Offset(0, 5).Value + .Offset(0,
8).Value + .Offset(0, 10).Value + .Offset(0, 12).Value
Else
.Offset(0, 13).Value = .Offset(0, 5).Value + .Offset(0,
8).Value + .Offset(0, 9).Value + .Offset(0, 12).Value
End If
Case Else:
'do nothing. Allow the entry in the E column but don't
bother with any other updates
End Select
End With
End If

XIT:
Application.EnableEvents = True

End Sub

-------------------------------------------------------------------
This works great.

Problem is, if I make any updates in any of the Columns H through Q, I have
to reselect Column E of the same row to get the code to fire.

I'm not quite sure how to update my code to have it trigger if any changes
occur in the Columns H through Q (as well as E) and then evaluate whats in
Column E and do the appropriate Calculation for the total budget.

Any guidance would be greatly appreciated.

Thanks

 

[Patrick Molloy]

it may just be

change this

Set rng = Intersect(Target, Range("E:E"))

to

Set rng = Intersect(Target, Range("E:E;H:Q"))

 

[Beans]

Hi Patrick, thanks for your suggestion, but I get a Run-Time Error '1004'
Method 'Range' of object'_Worksheet' failed.

Any suggestions.

Cheers

 

[Beans]

Thank you very much.... that did the trick.

Cheers