Macro will not read the value of cell with a formula in it!

grayson1231 · #1 03-28-2015, 11:07 AM

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!

charlesdh · #2 03-28-2015, 11:49 AM

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

grayson1231 · #3 03-28-2015, 11:56 AM

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

charlesdh · #4 03-28-2015, 12:06 PM

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

Then run it. Then try your worksheet code.

grayson1231 · #5 03-28-2015, 12:36 PM

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"

charlesdh · #6 03-28-2015, 12:39 PM

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.

charlesdh · #7 03-28-2015, 01:11 PM

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

grayson1231 · #8 03-28-2015, 01:23 PM

I previously tried that technique and it didn't work. I'm also not sure if I entered the code properly...

charlesdh · #9 03-28-2015, 01:36 PM

Hi,

As mentioned you can send the workbook to me. However, I'm about ready to take off for dinner.

charlesdh · #10 03-28-2015, 01:46 PM

Oops, I just noticed the you have
Target.Address(0, 0) set to 0,0. I think it needs to reference an actual address.

charlesdh · #11 03-28-2015, 03:47 PM

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

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