Worksheet_Change Event not working.

Hi, I'm using MS Excel 2010. I have a very simple TEST workbook with 2 worksheets (Sheet1 and Sheet2). I'm trying to do a proof of concept for a specific Event Handler, but it is not working as I expected.  

So...this is what I have:

Sheet2 contains a number in Cell(A1).  [Lets call this number the "Control Value")    

Sheet1 has a link to the Control Value number from Sheet2 at cell A1, which is ----   =Sheet2!A1
Sheet1 also has a bar chart (Chart 1) graphically representing the Control Value number.
Cell(A1) on Sheet1 is formatted as currency.

What I'm trying to do is change the Fill color on the chart's series collection from Red to Green as I go from negative to positive.  

Therefore, I have the following code in the VBA code-block of Sheet1:
---------------------------------------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)

    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(1).Select
   
    If Range("A1") < 0 Then
    With Selection.Format.Fill
        .ForeColor.RGB = RGB(255, 0, 0)  <<< Fill color = Red, if the number in =Sheet2!A1 is NEGATIVE
    End With
   
    Else
   
    With Selection.Format.Fill
        .ForeColor.RGB = RGB(98, 202, 90)  <<< Fill color = Green, if the number in =Sheet2!A1 is POSITIVE
    End With
   
    End If
End Sub
---------------------------------------------------------------------------------------------

 OK...so the chart actually does update the value when I make a change to the Control Value number, but NOT the color -- Why?

However, if I move the control value back to Sheet1 and reset the link for A1, then it work...the values update and the colors change from Red to Green as the value changes from negative to positive.

So, why does it work when everything is on one worksheet, but not when the control value is on a separate worksheet?  I need it to work having the control value on Sheet2.

Thanks,
Fulano
Mr_FulanoAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

a worksheet change event only fires when a cell is edited, not when a formula recalculates and returns another value.

You need to initiate the change event from the sheet where the number is being entered. The procedure will then need to activate the sheet with the chart and run the rest of the code.

Do you know that you can use a different color for negative values without using VBA? Set the fill color of the series to Solid Fill and tick the box to Invert if negative, then select your preferred colors for positive and negative values.

screenshot
cheers, teylyn

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mr_FulanoAuthor Commented:
Hi Teylyn, thank you for your suggestions. I will try them both and NO I did not know that trick about inverting if negative. I think that will solve my main problem although I still would like to make sure I can update a graph from another worksheet.

I'm going to give it a try and let you know. That said, I think you solved my problem. -- I'll close this out as soon as I test your suggestions.

Fulano
Mr_FulanoAuthor Commented:
Hi Teylyn, your second solution worked perfectly - and I learned something new! I'm still going to play around with my original question and I think your suggestions will lead me down a proper path, but for now...you solved my problem and answered my question perfectly! -- Thank you!

Fulano
Mr_FulanoAuthor Commented:
Very good suggestions and a perfect solution. EXCELLENT!!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.