Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with cell value
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 -- EMoe ------------------------------------------------------------------------ EMoe's Profile: http://www.excelforum.com/member.php...o&userid=23183 View this thread: http://www.excelforum.com/showthread...hreadid=386218 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with cell value
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 -- Regards, Tom Ogilvy "EMoe" wrote in message ... 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 -- EMoe ------------------------------------------------------------------------ EMoe's Profile: http://www.excelforum.com/member.php...o&userid=23183 View this thread: http://www.excelforum.com/showthread...hreadid=386218 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with cell value
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 -- steveB Remove "AYN" from email to respond "EMoe" wrote in message ... 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 -- EMoe ------------------------------------------------------------------------ EMoe's Profile: http://www.excelforum.com/member.php...o&userid=23183 View this thread: http://www.excelforum.com/showthread...hreadid=386218 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with cell value
I'll try this when I get back to work. Thanks, EMoe -- EMoe ------------------------------------------------------------------------ EMoe's Profile: http://www.excelforum.com/member.php...o&userid=23183 View this thread: http://www.excelforum.com/showthread...hreadid=386218 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell Underline problem underlines across width of cell | Excel Worksheet Functions | |||
Very Basic Problem - Merged Cell Equals Contents of a Single Cell | Excel Worksheet Functions | |||
Cell problem | Excel Worksheet Functions | |||
problem with cell reference in =sum(offset(cell reference,x,y,z,a)). Want cell ref to be variable. | Excel Worksheet Functions | |||
Last cell problem | Excel Programming |