Chart Formatting code query

[asgourlay]

I have written the attached code for a chart of mint that will change the colo(u)r of the data point dependent whether the actual value is above the budget. The code works fine if I have a breakpoint in it and step through the code but the moment I remove the breakpoint a Mismatch error (13) stops the code finishing at the bolded point?

Any Ideas

R

Private Sub worksheet_calculate()
Dim CHT As Chart
Dim SER_A As Series
Dim SER_B As Series
Dim I As Integer
Dim VAL_ACT As Variant
Dim VAL_BUD As Variant


Set CHT = Worksheets("Sheet1").ChartObjects("Chart 1").Chart
Set SER_B = CHT.SeriesCollection(1) ' BUDGET
Set SER_A = CHT.SeriesCollection(2) ' ACTUAL
P = 2
For I = 1 To 8
VAL_ACT = ActiveSheet.Cells(I + 1, "D")
VAL_BUD = ActiveSheet.Cells(I + 1, "C")
If VAL_ACT > VAL_BUD Then
' ACT > BUD
SER_A.Points(I).Interior.ColorIndex = 50: Pattern = xlSolid
Else
' BUD > ACT
SER_A.Points(I).Interior.ColorIndex = 3: Pattern = xlSolid
End If
P = P + 1
Next I
End Sub

 

[Andrew Poulsom]

Strange. What happens with:

VAL_ACT = ActiveSheet.Cells(I + 1, "D").Value

 

[asgourlay]

same problem

did as suggested but same problem. I have removed the dead variable 'P' and also changed the type of VAL_ACT & VAL_BUD to double as that was what was coming through on the Locals window but exactly the same Mismatch error at the same point, but again not if there is a breakpoint and I'm stepping through.

Wierd

R

 

[Andrew Poulsom]

How about?

VAL_ACT = ActiveSheet.Cells(I + 1, 4).Value

 

[asgourlay]

Doh!!!

That works

Thanks

R