Nesting worksheet_Calculate inside worksheet_CHANGE

[Guest]

I am not fluent with VBA and need your help to correct this code please.

I got this code from a forum. It changes the color of a chart based on a
condition. It does exactly what I want, but because it uses
worksheet_calculate, this code only trigers when I execute the code in the VB
tool... it does not change on the fly when a cell is changed.

I was told by someone on in a forum that I should nest this code below
insode a Worksheet_CHANGE... to that when the cell (AG2) changes that the
worksheet_calculate code is executed. I do not know how to nest these.

Can you help you please?

----
Private Sub Worksheet_Calculate()
Dim chrt As ChartObject
Dim i As Integer
For i = 1 To Sheets.Count
With Sheets(i)
For Each chrt In .ChartObjects
If IsNumeric(Me.Range("AG1").Value) Then
If Me.Range("AG1").Value > 0.1 Then
chrt.Chart.ChartArea.Interior.ColorIndex = 3
Else
chrt.Chart.ChartArea.Interior.ColorIndex _
= xlAutomatic
End If
End If
Next chrt
End With
Next i
End Sub

 

[Franz Verga]

I am not fluent with VBA and need your help to correct this code
please.

I got this code from a forum. It changes the color of a chart based
on a condition. It does exactly what I want, but because it uses
worksheet_calculate, this code only trigers when I execute the code
in the VB tool... it does not change on the fly when a cell is
changed.

I was told by someone on in a forum that I should nest this code below
insode a Worksheet_CHANGE... to that when the cell (AG2) changes that
the worksheet_calculate code is executed. I do not know how to nest
these.

[cut]

Hi Justin,

you have just to change the event from Calculate to Change and check what is
changed... So your code becomes:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim chrt As ChartObject
Dim i As Integer

If Not Intersect (Target, "AG2") is Nothing Then

For i = 1 To Sheets.Count
With Sheets(i)
For Each chrt In .ChartObjects
If IsNumeric(Me.Range("AG1").Value) Then
If Me.Range("AG1").Value > 0.1 Then
chrt.Chart.ChartArea.Interior.ColorIndex = 3
Else
chrt.Chart.ChartArea.Interior.ColorIndex _
= xlAutomatic
End If
End If
Next chrt
End With
Next i

Else

Exit Sub

End If

End Sub


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[Guest]

I get compiler errors when i use this code.

I am not fluent with VBA and need your help to correct this code
please.

I got this code from a forum. It changes the color of a chart based
on a condition. It does exactly what I want, but because it uses
worksheet_calculate, this code only trigers when I execute the code
in the VB tool... it does not change on the fly when a cell is
changed.

I was told by someone on in a forum that I should nest this code below
insode a Worksheet_CHANGE... to that when the cell (AG2) changes that
the worksheet_calculate code is executed. I do not know how to nest
these.

[cut]

Hi Justin,

you have just to change the event from Calculate to Change and check what is
changed... So your code becomes:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim chrt As ChartObject
Dim i As Integer

If Not Intersect (Target, "AG2") is Nothing Then

For i = 1 To Sheets.Count
With Sheets(i)
For Each chrt In .ChartObjects
If IsNumeric(Me.Range("AG1").Value) Then
If Me.Range("AG1").Value > 0.1 Then
chrt.Chart.ChartArea.Interior.ColorIndex = 3
Else
chrt.Chart.ChartArea.Interior.ColorIndex _
= xlAutomatic
End If
End If
Next chrt
End With
Next i

Else

Exit Sub

End If

End Sub


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[Franz Verga]

I get compiler errors when i use this code.

If you like you could try to upload your fie to www.rapidshare.de...


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[Guest]

get the file here:
http://www2.ibackup.com/qmanager/servlet/share?key=qozde71198

 

[Franz Verga]

get the file here:
http://www2.ibackup.com/qmanager/servlet/share?key=qozde71198

Hi Justin,

I made a mistake in the code, sorry...

The right version is this (I assume also that you want to change the chart
area color when AG1 change, as stated in your code and not when AG2 change,
as you stated in your post...):

Private Sub Worksheet_Change(ByVal Target As Range)

Dim chrt As ChartObject
Dim i As Integer

If Not Intersect(Target, Range("AG1")) Is Nothing Then

For i = 1 To Sheets.Count
With Sheets(i)
For Each chrt In .ChartObjects
If IsNumeric(Me.Range("AG1").Value) Then
If Me.Range("AG1").Value > 0.1 Then
chrt.Chart.ChartArea.Interior.ColorIndex = 3
Else
chrt.Chart.ChartArea.Interior.ColorIndex _
= xlAutomatic
End If
End If
Next chrt
End With
Next i

Else

Exit Sub

End If

End Sub

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy