Conditional Formatting of a CHART AREA COLOR

[jluyt]

I need your help please!

I am not very familiar with VBA so please if you answer give me as much detail as possable.

I have an XLS which I need to do 2 things with.

I have a series of vallues (rows). These values are entered every day for 1 month. in the image you see 1 2 3 4 5 (headers) - these are days and can be changed to DATE, in order to use TODAY in VBA. Every day i colum of values are entered manualy (1 colum). These values map to 8 charts (not all rows have charts, just the gray ones). In the XLS you see the gray hilighted rows which are the values the chart uses.

1) I need to automaticly calculate the LAST INCREASE in value. Meaning the amount the value increased or decresed TODAY, since yesterday. If this increase is more than 10% then...

2) conditional format and change the chart AREA COLOR to RED if the increase is greater than 10%.

I attach a picture for you too.

Please help - I appreciate your time.

[XLGibbs]

Have you tried anything with respect to VBA yet?

This is a complicated piece, and there are different ways to interprest "automatically", especially when new data is entered daily.

The part about determining the difference in values and changing the format of the chart areas isn't too bad....

Please upload an actual excel file, otherwise...help will be difficult as most of us don't have the time to build the sheet into excel based on the picture. Sanitize the data to remove sensitive information, but leave enough that a reasonable effort can be made on your behalf.

[jluyt]

I am close. I am using the code below to make the change of color in the chart... it works pretty well, but it only changes the chatr color when i execute the code via the VB editor. HJow can I get it to execute when the cell changes?

CODE:
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
========================

Any ideas how it will load/execute when the value in the cell changes?

[XLGibbs]

If you want it to change when you type a new value into a cell, you need to use Worksheet_Change event, which will fire whenever any cell is changed. Just include logic to make sure it only fires when a relevant cell is changed.

PS. Avoid using calculate events.....just trust me on that. You can likely put the routine you wrote into a sheet_Change event, but nest it inside logic to evaluate the cell being changed first.

[jluyt]

I do not know VBA that well. I got this code from a forum and it is the best i have so far.

Can you please show me how you sugest I change the code?

justin

[Andy Pope]

I posted this reply in the MS charting newsgroup.

Hi,

If your chart is on a worksheet you don't need to use VBA at all.
Make use of conditional formatting instead. Position the chart over a cell or range of cells. Then format the chart area to have no fill. This will make the chart transparent such that when the chart is not active the cells beneath are visible. Then when CF is applied any colour will show through.

Cheers
Andy

Attached is an example.
I doubt my formula in A2 is correct but you say you already have a formula in AG so simply adjust the CF formula.

In order to get the best effect you have to lose the shadow and rounded corners.