Justin Luyt wrote:
get the file he
http://www2.ibackup.com/qmanager/ser...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


"Justin Luyt" wrote:

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


----
Justin