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!