If you have formulas in B14 and B15 that get reevaluated with changes in B12 and
B13, then you don't want to use the worksheet_change event.

You'd want to use the worksheet_calculate event.

And I think you'd want something like:

Option Explicit
Private Sub Worksheet_Calculate()
Me.Range("G7").Value = Application.Sum(Me.Range("B15:AF15"))
End Sub

But I don't understand why you wouldn't just use:
=sum(b15:af15)
in G7 and drop the event code completely.

(The Me keyword represents the object that holds the codes. In this case, it's
the worksheet that's being recalculated.)

mkarja wrote:

Hi,

I'm trying to make an excel macro that would do something
when a certain cells (B15:AF15) value changes. The problem is
that the code won't detect all the changed cells.
When I for example make a change in cells B12 or B13 then it
would change the values of cells B14 & B15 so in total three
changed cells. The code should calculate the sum of range of
cells from B15 to AF15.
Here's the code.
--------------------------
Private Sub Worksheet_Change(ByVal Target As Range)

Dim janRange As Range
Dim lngCell As Long
Dim rowIndex As Long
Dim targetCount As Long

Set janRange = Sheet1.Range("B15:AF15")

rowIndex = 1

For lngCell = 1 To janRange.Count
For targetCount = 1 To Target.Cells.Count
If janRange(rowIndex, lngCell).Address = Target.Address Then
Sheet1.Cells(7, 7).Value = WorksheetFunction.Sum(janRange)
End If
Next targetCount
Next lngCell

End Sub
----------------------------------------------
Now it never goes inside that If statement.
The Target.Cells.Count is allways 0.

I could of course do those things manually that I'm trying to do
with code now, but where's the fun in that would be :)

I would appreciate any help with this.

----
mkarja

--

Dave Peterson