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

 

[gino59]

Ah shg - my apologies! I obviously suffered a major brain cramp and have been slow to recover!

DUH, DUH, DUH!! Yep, stick that snippet of code into the Calendar click module and as my french auntie used to say "Vee-ola"!!

Tough being self-taught by a dolt!! Obviously, the day has been too long and I am in much need of copious amounts of lager...

Thanks for your patience!

Cheers!
Gino

 

[shg]

Good job, Gino. All's well that ends well.