Problem with cell value

[EMoe]

Hello,
I got this code to work with the help of some of the experts from this
forum.
What is does is changes the color of a shape based on the value of a
cell. However, I've found that if I type 1 in the cell, the color
changes. If I type 0, the shape color changes like it should. The
problem is that when I put a formula in that cell that would return the
value of 1 or 0, nothing happens. I think this code is looking at the
formula, and not the value.

How do I alter this code so that I would respond to the cell value, and
not the formula. Or is there another code I could use.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("A1")) Is Nothing Then
With Me.Shapes(AutoShape1).Fill.ForeColor
Select Case Me.Range("A1").Value
Case 1: .SchemeColor = 2
Case 0: .SchemeColor = 12
Case Else: .SchemeColor = 11
End Select
End With
End If
End Sub

Thanks,
EMoe

 

[Tom Ogilvy]

You need to use the Calculate event, not the Change event.

Private Sub Worksheet_Calculate()
With Me.Shapes(AutoShape1).Fill.ForeColor
Select Case Me.Range("A1").Value
Case 1: .SchemeColor = 2
Case 0: .SchemeColor = 12
Case Else: .SchemeColor = 11
End Select
End With
End Sub

 

[STEVE BELL]

With a formula in a cell -
when the results of the formula change it does not activate the change
event

but it will activate the calculate event

in ThisWorkbook module
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)

End Sub

or
in the sheet module
Private Sub Worksheet_Calculate()

End Sub

or you can build some kind of compare or equals statement into your
change event
If Range("A1") = 0 then

elseif Range("A1") = 1 then

end if

 

[EMoe]

I'll try this when I get back to work.

Thanks,
EMoe