Baffled! Formula sometimes won't change shape colors!

[gino59]

Hi all,

Hoping someone can point me in the right direction! I have the following lines in a module that sometimes work and sometimes don't... no rhyme or reason that I can see!

Basically, if the value in E17 is less than 0 change the shape color to red and if the value is greater than or equal, change it to green.

Sounds simple, eh? Not sure why sometimes it works and sometimes it doesn't! It's almost like once it turns green, even if the value gets changed to less than 0, the shape doesn't update.

Here's the code...

     If ActiveSheet.Range("E17").Value < 0 Then
          ActiveWorkbook.ActiveSheet.Shapes(4).Fill.ForeColor.SchemeColor = 16
               Else
          ActiveWorkbook.ActiveSheet.Shapes(4).Fill.ForeColor.SchemeColor = 17
     End If
     If ActiveSheet.Range("E21").Value < 0 Then
          ActiveWorkbook.ActiveSheet.Shapes(5).Fill.ForeColor.SchemeColor = 16
               Else
          ActiveWorkbook.ActiveSheet.Shapes(5).Fill.ForeColor.SchemeColor = 17
     End If

The values in cells E17 and E21 are formula generated and formatted as currency. Any ideas or suggestions greatly welcomed!!

Cheers,
Gino

 

[excel_vba_guru]

Try clearing out the fill color first, then setting to 16 or 17...

 

[shg]

    With ActiveSheet
        .Shapes(4).Fill.ForeColor.SchemeColor = IIf(.Range("E17").Value < 0, 16, 17)
        .Shapes(5).Fill.ForeColor.SchemeColor = IIf(.Range("E21").Value < 0, 16, 17)
    End With

What causes the code to run?

 

[gino59]

excel_vba_guru - can you tell me how to set the color to clear? I get white or black.

shg - that's much better code but the result is still the same. Basically, the user selects a date from a calendar which then runs some array formulae to retrieve transactions for that date. The transactions have positive and negative cash values. the cash values are put in the cells and the shapes point to them. So if I select 11/1/2010 in the calendar, the sheet populates a table with the transactions for that day. Cell E17 calculates the daily cash flow (+/-) and returns a single $ value. Shape 4 is set to check the value and if - turn red and if positive turn green.

It seems to work the first time and then stop, so I'm wondering if before checking with the IF statements, I shoould force a recalc of the sheet?

Thanks for the suggestions!!

Cheers,
Gino

 

[shg]

There should be no need to change the shape colors to anything else.

So the user changes something, and the sheet calculates (assuming calculation is set to automatic) -- what causes the code to run?

 

[gino59]

Thanks, shg! The calc is set to automatic and the $ value in the shape changes with no issue. I'm not sure the shape is getting "cued" that when the value of E17 changes, check it and color as appropriate. It seems to the first time the code runs, but then it stays "stuck" on that color so I'm thinking I need something to tell the shape that when the value in E17 changes, check it and then color.

Clear as mud, eh? I hope that explains what's happening.

 

[shg]

Gino,

Your code is in some procedure. What causes that procedure to run?

For example, if it's attached to a button, then you have to push the button.

 

[gino59]

Hi shg - the code is in the worksheet and is run when the user clicks a date in the calendar form (Private Sub Worksheet_Activate(). Maybe that should be Calculate?

Thanks so much!
Gino

 

[shg]

If it runs only on Worksheet_Activate, then it won't run again until you deselect the sheet and then select it again.

I don't understand how that relates to clicking a date on a calendar form, though. If that's what spawns the change, then add that code to the click event for the form.

Are you thinking that the Worksheet_Activate event fires when you select something on the sheet after having the focus on the form? It doesn't.

 

[excel_vba_guru]

excel_vba_guru - can you tell me how to set the color to clear? I get white or black.

Try this...
ActiveSheet.Shapes(4).Fill.Visible = msoFalse