VBA change shape color based on cell value

[noelmus]

Hi
I am using a code so that the shape changes the color when a cell value is changed.
My problem is that cell J24 is changing as a result of a formula recalculating. So what I have to change in my code shown below to make it work?
I prefer that the code will work for 2 shapes since I need to add another shape.

Thanks in advance

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("J24")) Is Nothing Then Exit Sub
If IsNumeric(Target.Value) Then
If Target.Value < 80000 Then
ActiveSheet.Shapes("LevelA").Fill.ForeColor.RGB = vbRed
ElseIf Target.Value >= 80000 And Target.Value < 400000 Then
ActiveSheet.Shapes("LevelA").Fill.ForeColor.RGB = vbYellow
Else
ActiveSheet.Shapes("LevelA").Fill.ForeColor.RGB = vbGreen
End If
End If
End Sub

 

[mumps]

I'm not sure what you mean. Can you give me an example?

 

[noelmus]

With my original code if tank is half full which in my case is 200000, the shape appears as half with color and half unfill. It must look like the image. see link.
https://www.dropbox.com/s/nwij781glhyi1dv/tank.jpg?dl=0

 

[mumps]

Try:

Private Sub Worksheet_Calculate()
    Static LastValueJ, LastValueH
    Dim shp As Shape
    With Range("j24")
        If LastValueJ <> .Value Then
            AdjustTank .Value, "A"
            LastValueJ = .Value
        End If
    End With
    With Range("H24")
        If LastValueH <> .Value Then
            AdjustTank .Value, "B"
            LastValueH = .Value
        End If
    End With
    For Each shp In ActiveSheet.Shapes
        If shp.Name = "TankA" Then
            If IsNumeric(Range("J24")) Then
                If Range("J24").Value < 80000 Then
                   shp.Fill.ForeColor.RGB = vbRed
                ElseIf Range("J24").Value >= 80000 And Range("J24").Value < 400000 Then
                   shp.Fill.ForeColor.RGB = vbYellow
                Else
                    shp.Fill.ForeColor.RGB = vbGreen
                End If
            End If
        ElseIf shp.Name = "TankB" Then
            If IsNumeric(Range("H24")) Then
                If Range("H24").Value < 80000 Then
                   shp.Fill.ForeColor.RGB = vbRed
                ElseIf Range("H24").Value >= 80000 And Range("H24").Value < 400000 Then
                   shp.Fill.ForeColor.RGB = vbYellow
                Else
                    shp.Fill.ForeColor.RGB = vbGreen
                End If
            End If
        End If
    Next shp
End Sub

 

[noelmus]

Sorry mumps but it didn't work. Tanks still shown with full fill when the value is not full.
Thanks

 

[noelmus]

Hi mumps,
Is there any update?

Thanks

 

[mumps]

Click here to download your file. Please let me know how it works out.

 

[mumps]

Please see Post# 26.

 

[noelmus]

Hi mumps,
Thanks a lot and great job.

 

[mumps]

My pleasure.