vba code does not run when data in a cell is changed by a formula

[ataloss]

I have vba code that changes the color of 2 shapes depending on the results of a formula in a cell (L11) the output is a numeric value of 0, 1 or 2. The formula is the following:

=if(b251="Current Owned",1,if(h13>0,2,0))

​

However, when I run the code, it works only if I delete the formula and manually enter the values in the cell. Can you help me with this because I don't understand why this is happening. This is the vba code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("L11")) Is Nothing Then
If IsNumeric(Target.Value) Then
If Target.Value = 0 Then
ActiveSheet.Shapes.Range(Array("arrow 1")).Select
With Selection.ShapeRange.Line
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorBackground1
.ForeColor.TintAndShade = 0
.ForeColor.Brightness = -0.349999994
.Transparency = 0
ActiveSheet.Shapes.Range(Array("arrow 2")).Select
With Selection.ShapeRange.Line
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorBackground1
.ForeColor.TintAndShade = 0
.ForeColor.Brightness = -0.349999994
.Transparency = 0
End With
End With
End If
If Target.Value = 1 Then
ActiveSheet.Shapes.Range(Array("arrow 1")).Select
Selection.ShapeRange.ZOrder msoBringForward
With Selection.ShapeRange.Line
.Visible = msoTrue
.ForeColor.RGB = RGB(255, 0, 0)
.Transparency = 0
ActiveSheet.Shapes.Range(Array("arrow 2")).Select
With Selection.ShapeRange.Line
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorBackground1
.ForeColor.TintAndShade = 0
.ForeColor.Brightness = -0.349999994
.Transparency = 0
End With
End With
End If
If Target.Value = 2 Then
ActiveSheet.Shapes.Range(Array("arrow 1")).Select
With Selection.ShapeRange.Line
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorBackground1
.ForeColor.TintAndShade = 0
.ForeColor.Brightness = -0.349999994
.Transparency = 0
ActiveSheet.Shapes.Range(Array("arrow 2")).Select
Selection.ShapeRange.ZOrder msoBringForward
With Selection.ShapeRange.Line
.Visible = msoTrue
.ForeColor.RGB = RGB(255, 0, 0)
.Transparency = 0
End With
End With
End If
End If
End If
Range("H13").Select


End Sub

Any help to solve this is very much appreciated!

 

[Joe4]

The Worksheet_Change event is only triggered upon a physical change in the cell, that is a new entry into the particular cell or copy/paste into that cell. It is NOT triggered upon the value that a formula returns changing.

So you have a few options:
- Change the Worksheet_Change event so that it is looking for changes in the cells that your formula is based on (i.e. b251 an d h13)
- Use a Worksheet_Calculate event instead (will involve changes in your, as "Target" is not used in Worksheet_Calculate events)

 

[Dryver14]

You shouldn't have to delete the formula but what you can do is press the f9 key to force calculations on the sheet

 

[Joe4]

You shouldn't have to delete the formula but what you can do is press the f9 key to force calculations on the sheet

Pressing F9 will force calculations, but it still will not invoke a Worksheet_Change event procedure.
As I mentioned, Worksheet_Change event procedures are not triggered by calculations.

 

[ataloss]

Thank you for your recommendations. My next question would be how do I do either? That is now way over my head. I was able to get this far by reading through all the posts and creating the vba code by piecing things together. I would be lying if I said I fully understood what the code says. Do I need to create another Private Sub?
Is there a link you can refer me to that teaches how to do this?

Thank you again for your response.

 

[Michael M]

Assuming Range("L11").was your target range....

Sub Worksheet_Calculate()
If IsNumeric(Range("L11")) Then
If Range("L11").Value = 0 Then
ActiveSheet.Shapes.Range(Array("arrow 1")).Select
With Selection.ShapeRange.Line
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorBackground1
.ForeColor.TintAndShade = 0
.ForeColor.Brightness = -0.349999994
.Transparency = 0
ActiveSheet.Shapes.Range(Array("arrow 2")).Select
With Selection.ShapeRange.Line
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorBackground1
.ForeColor.TintAndShade = 0
.ForeColor.Brightness = -0.349999994
.Transparency = 0
End With
End With
End If
If Range("L11").Value = 1 Then
ActiveSheet.Shapes.Range(Array("arrow 1")).Select
Selection.ShapeRange.ZOrder msoBringForward
With Selection.ShapeRange.Line
.Visible = msoTrue
.ForeColor.RGB = RGB(255, 0, 0)
.Transparency = 0
ActiveSheet.Shapes.Range(Array("arrow 2")).Select
With Selection.ShapeRange.Line
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorBackground1
.ForeColor.TintAndShade = 0
.ForeColor.Brightness = -0.349999994
.Transparency = 0
End With
End With
End If
If Range("L11").Value = 2 Then
ActiveSheet.Shapes.Range(Array("arrow 1")).Select
With Selection.ShapeRange.Line
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorBackground1
.ForeColor.TintAndShade = 0
.ForeColor.Brightness = -0.349999994
.Transparency = 0
ActiveSheet.Shapes.Range(Array("arrow 2")).Select
Selection.ShapeRange.ZOrder msoBringForward
With Selection.ShapeRange.Line
.Visible = msoTrue
.ForeColor.RGB = RGB(255, 0, 0)
.Transparency = 0
End With
End With
End If
End If
Range("H13").Select
End Sub

 

[Joe4]

You can only have one Worksheet_Change event procedure macro per sheet. You would just need to modify what you have.

Here is your first line of code:

[COLOR=#333333]If Not Intersect(Target, Range("L11")) Is Nothing Then[/COLOR]

The contents of L11 is this formula:

[B]=if(b251="Current Owned",1,if(h13>0,2,0))[/B]

So how is the value in L11 changed? If this the value in H13 or B251 is changed.
So, the question is, are H13 and B251 formulas also, or are they hard-coded values?
If they are hard-coded values, then these are the ranges we want to check for changes, not L11.
That would look something like this:

[COLOR=#333333]If (Not Intersect(Target, Range("B251")) Is Nothing) Or ([/COLOR][COLOR=#333333]Not Intersect(Target, Range("H13")) Is Nothing)[/COLOR][COLOR=#333333] Then[/COLOR]

 

[ataloss]

Thank you so much for all your help!!!

 

[ataloss]

Thank you!!! Thank you!!!! Thank you!!! That worked like a charm. The more I learn the more I realize I know nothing!