#1
|
|||
|
|||
Macro will not read the value of cell with a formula in it!
I'm new to macros and I currently have this macro in my workbook! I have this code multiple times in the macro referencing different cells in the sheet. My question is this - when I first started I didn't have any formulas in the referencing cell and this macro worked great! Now, I have a formula in cell "ES4" and the macro has stopped working. Can anyone help lead me in the right direction to get this macro to read the calculated value from the formula in cell "ES4"? And automatically recognize it the change in value based on what the formula retrieves? I can't seem to find a way to get this to recognize the value now that a formula is in "ES4"! Help is much appreciated!
The formula located in "ES4","ES5",etc. is a basic "=IFERROR(INDEX(MATCH" type formula. This is simply retrieving a percentage from a table in the workbook. Example of the macro. Again, this macro keeps going in the same repetitive format just referencing different cells and connecting the referencing cell to it's proper corresponding shape. Here is what I have so far. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address(0, 0) = "ES4" Then If Target.Value >= 0.1 Then ActiveSheet.Shapes("LHPHard1_1").Fill.ForeColor.RG B = RGB(192, 0, 0) ElseIf Target.Value >= 0.07 Then ActiveSheet.Shapes("LHPHard1_1").Fill.ForeColor.RG B = RGB(218, 150, 148) ElseIf Target.Value <= 0.04 Then ActiveSheet.Shapes("LHPHard1_1").Fill.ForeColor.RG B = RGB(0, 0, 255) Else ActiveSheet.Shapes("LHPHard1_1").Fill.ForeColor.RG B = RGB(166, 166, 166) End If End If If Target.Address(0, 0) = "ES5" Then If Target.Value >= 0.1 Then ActiveSheet.Shapes("LHPHard2_1").Fill.ForeColor.RG B = RGB(192, 0, 0) ElseIf Target.Value >= 0.07 Then ActiveSheet.Shapes("LHPHard2_1").Fill.ForeColor.RG B = RGB(218, 150, 148) ElseIf Target.Value <= 0.04 Then ActiveSheet.Shapes("LHPHard2_1").Fill.ForeColor.RG B = RGB(0, 0, 255) Else ActiveSheet.Shapes("LHPHard1_1").Fill.ForeColor.RG B = RGB(166, 166, 166) End If End If The ultimate goal is for the macro is read the cells when the formula updates the data. Right now it is only reading cells that I manually input and NOT reading the cells data if a formula is in it. Any help is much appreciated! |
#2
|
|||
|
|||
Hi,
Possible solution is to have this bit of code "If Target.Address(0, 0)" refer to "Text" "If Target.Address(0, 0).text". Update to add more info. When using code as you have you should use the "Application.EventDisable = true" This should stop the code from repeating itself. Code:
Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False ''''' then reset Event at the end of your macro Application.EnableEvents = True End Sub Last edited by charlesdh; 03-28-2015 at 12:02 PM. Reason: update to correct code |
#3
|
|||
|
|||
Thank you for the reply. Unfortunately, it did not work. I simply added the ".Text" without making any other changes.
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address(0, 0).Text = "ES4" Then If Target.Value >= 0.1 Then ActiveSheet.Shapes("LHPHard1_1").Fill.ForeColor.RG B = RGB(192, 0, 0) ElseIf Target.Value >= 0.07 Then ActiveSheet.Shapes("LHPHard1_1").Fill.ForeColor.RG B = RGB(218, 150, 148) ElseIf Target.Value <= 0.04 Then ActiveSheet.Shapes("LHPHard1_1").Fill.ForeColor.RG B = RGB(0, 0, 255) Else ActiveSheet.Shapes("LHPHard1_1").Fill.ForeColor.RG B = RGB(166, 166, 166) End If End If |
#4
|
|||
|
|||
Hi,
If the code failed you may need to "Reset" it. The problem with worksheet function is there are a pain to use. In the same module try this code to reset the worksheet event. Code:
Sub Reset_worksheet Application.EnableEvents = True end sub Last edited by charlesdh; 03-28-2015 at 12:10 PM. Reason: Brain faster than writing...corrected info |
#5
|
|||
|
|||
Nothing yet. Can it be something at the top?
Worksheet_Calculate instead of Worksheet_Change I feel as if it has something to do with this event but can't figure out the corresponding moves once I make the change to "Calculate" |
#6
|
|||
|
|||
Hi,
If you wish you can send me the workbook to look at it. See PM for my email.. Or, if you can attach the workbook. |
#7
|
|||
|
|||
Hi,
Your reference to "Calculate" you may try something along this order. Code:
Private Sub Worksheet_Calculate() Static oldval If Range("C1").Value <> oldval Then oldval = Range("C1").Value ' 'rest of your code here ' End If End Sub |
#8
|
|||
|
|||
I previously tried that technique and it didn't work. I'm also not sure if I entered the code properly...
|
#9
|
|||
|
|||
Hi,
As mentioned you can send the workbook to me. However, I'm about ready to take off for dinner. |
#10
|
|||
|
|||
Oops, I just noticed the you have
Target.Address(0, 0) set to 0,0. I think it needs to reference an actual address. |
#11
|
|||
|
|||
Hi,
Not tested. You should get the ideal. Code:
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$ES$4" And Target.Value >= 0.1 Then Application.EnableEvents = False ActiveSheet.Shapes("LHPHard1_1").Fill.ForeColor.RG B = RGB(192, 0, 0) ElseIf Target.Value >= 0.07 Then ActiveSheet.Shapes("LHPHard1_1").Fill.ForeColor.RG B = RGB(218, 150, 148) ElseIf Target.Value <= 0.04 Then ActiveSheet.Shapes("LHPHard1_1").Fill.ForeColor.RG B = RGB(0, 0, 255) Else ActiveSheet.Shapes("LHPHard1_1").Fill.ForeColor.RG B = RGB(166, 166, 166) End If End If If Target.Address = "$ES$5" And Target.Value >= 0.1 Then Application.EnableEvents = False ActiveSheet.Shapes("LHPHard2_1").Fill.ForeColor.RG B = RGB(192, 0, 0) ElseIf Target.Value >= 0.07 Then ActiveSheet.Shapes("LHPHard2_1").Fill.ForeColor.RG B = RGB(218, 150, 148) ElseIf Target.Value <= 0.04 Then ActiveSheet.Shapes("LHPHard2_1").Fill.ForeColor.RG B = RGB(0, 0, 255) Else ActiveSheet.Shapes("LHPHard1_1").Fill.ForeColor.RG B = RGB(166, 166, 166) End If End If Application.EnableEvents = True End Sub |
Tags |
macro |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Read cell from one table into another | donaldadams1951 | Word VBA | 2 | 02-04-2015 06:46 PM |
Macro to copy formula from one cell to another | anwar | Excel Programming | 1 | 04-25-2014 08:27 PM |
Formula in cell b1 using cell a1 if a1 is over certain number | pumkinbug87 | Excel | 5 | 12-03-2013 12:34 PM |
Change formula cell range based on cell value | Scoth | Excel | 4 | 10-25-2012 07:51 AM |
how to read R1C1 formula | Suhanti | Excel | 2 | 10-16-2010 11:30 AM |