you could simplify your loop

----------------------------------------
If Not Application.Intersect(target, janRange)) Is Nothing Then
' Do my funky thing.
end if
----------------------------------------

More worrying, its' impossible for Target.Cells.Count to = 0 in the
Worksheet_Change event.


In your description though you are implying that you have formula in cells
b14 & b15. Worksheet_Change doesn't consider changes to these formula's as
changes.

but you are more interested in the precedents, so

If Not Application.Intersect(Target, janRange.Precedents) Is Nothing Then
' Do my funky things
End If







"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