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

 

[noelmus]

Hi,
Sorry at this moment I can't do this since I'm at work but tomorrow I will give you the link and details.
Till now thank you for your help.
Happy Feasts

 

[mumps]

Same to you.

 

[noelmus]

Hi mumps,
The file has my original code since when I did yours, the tanks where not being updated.

Details:


This file is just for testing, so please ignore things that are not in place and I replaced some figures so that you can work without problems.
Tank A
This must be equal to cell J24. To bring the tank less than 80000 you must write figures in range A15 to A22 which at least bring a total of 320001 in cell A23 and to bring the tank more than 80000 you need at least a total of 320000 or less.


Tank B
This must be equal to cell H24 which appears when you write figures in cell G24. Now since this is just for testing you can write only figures from 4000 to 4010 to get the tank less than 80000 and 4020 to 4120 to get the tank from 80000 to 400000.


So tanks under 80000 will become RED, 80000 to 399999 will become YELLOW and from 400000 become GREEN.
If you need more information, please let me know.

File Link: https://www.dropbox.com/s/l420pebydge1nm7/Tanks.xlsm?dl=0


Thanks

 

[mumps]

Try:

Private Sub Worksheet_Calculate()
    Static LastValueJ, LastValueH
    Dim shp As Shape
    If IsNumeric(Range("J24")) Then
        For Each shp In ActiveSheet.Shapes
            If shp.Name Like "Tank*" 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
        Next shp
    End If

    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
End Sub

 

[noelmus]

Hi mumps,
Tank A is working good.
The problem in Tank B is that when Tank A becomes yellow, Tank B appears as yellow and if Tank A becomes red, Tank B appears as red.
To give you an example if Tank B has 40000 and Tank A has 100000 both became yellow. In this example Tank B must appears in red.


mumps when comparing between us I'm very far from practising.


Thanks in advance

 

[mumps]

Give this a try:

Private Sub Worksheet_Calculate()
    Static LastValueJ, LastValueH
    Dim shp As Shape
    
    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

    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
End Sub

 

[noelmus]

Hi mumps,
BULLSEYE
That means that you are PERFECT.
If you don't mind another question.
If I want to change color like from yellow to another color, what I have to do? I saw something like this =RGB(22,1,200)

Regards

 

[mumps]

If you go to this link: http://big-coronasdklua.blogspot.com/2011/04/rgb.html you will see a color chart with the corresponding RGB values for each color. Choose a color and use the RGB values in your code as in this example:

 shp.Fill.ForeColor.RGB = RGB(50, 205, 50)

I hope this helps.

 

[noelmus]

mumps
First of all thanks for the color code but I recognized that the level of the color of both tanks is not corresponding to the value.
Regards

 

[noelmus]

Hi mumps,
Don't know if you saw my last post. If you have a look to the file (link below), you see that the tanks' level do not correspond to the value.
Link: https://www.dropbox.com/s/c2c0bb9qkp22fh9/Tanks.xlsm?dl=0

Thanks in advance