VBA change colour of icon (graphic) based on cell value

[drefiek2]

Hi, I need help with a code which will change the colour of an icon (Graphic 9) to a different colour (detailed below in RBG format), depending on whether cell U5 has the following text:
"NIL" - red 0, green 176, blue 80
"LOW" - red 255, green 192, blue 0
"MED" - red 198, green 89, blue 17
"HIGH" - red 255, green 0, blue 0

If this will not work with icon graphics, is it worth converting them to a shape? Any help would be much appreciated, thank you.

 

[bebo021999]

How and where did you get Graphic Icon?

 

[drefiek2]

How and where did you get Graphic Icon?

Insert > Icons
It shows in the Name Box as 'Graphic #'

I can convert the icon to a shape if VBA will not work with icons/graphics.

 

[drefiek2]

I figured it out. Note the RGB values can be changed to get the colour you want!

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("U5") = "NIL" Then
ActiveSheet.Shapes.Range(Array("Graphic 57")).Select
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(255, 0, 0)
Else
If Range("U5") = "LOW" Then
ActiveSheet.Shapes.Range(Array("Graphic 57")).Select
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(0, 255, 0)
Else
If Range("U5") = "MED" Then
ActiveSheet.Shapes.Range(Array("Graphic 57")).Select
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(0, 0, 255)
Else
If Range("U5") = "HIGH" Then
ActiveSheet.Shapes.Range(Array("Graphic 57")).Select
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(255, 239, 0)
End If
End If
End If
End If
End Sub

 

[bebo021999]

Nice to hear it works.
However your code should be in neater and shorter way with "With" and "Select Case"
Like this:

ActiveSheet.Shapes.Range(Array("Graphic 57")).Select
With Selection
     Select case  Range("U5") 
          Case  "NIL"
                     ShapeRange.Fill.ForeColor.RGB = RGB( 0, 255, 0)
          Case  "LOW"
                     ShapeRange.Fill.ForeColor.RGB = RGB( 0, 0, 255)
          Case  "MED"
                     ShapeRange.Fill.ForeColor.RGB = RGB(255, 0, 0)
          Case  "HIGH"
                     ShapeRange.Fill.ForeColor.RGB = RGB(255, 239, 0)
     End Select
End With

 

[drefiek2]

Nice to hear it works.
However your code should be in neater and shorter way with "With" and "Select Case"
Like this:

ActiveSheet.Shapes.Range(Array("Graphic 57")).Select
With Selection
     Select case  Range("U5")
          Case  "NIL"
                     ShapeRange.Fill.ForeColor.RGB = RGB( 0, 255, 0)
          Case  "LOW"
                     ShapeRange.Fill.ForeColor.RGB = RGB( 0, 0, 255)
          Case  "MED"
                     ShapeRange.Fill.ForeColor.RGB = RGB(255, 0, 0)
          Case  "HIGH"
                     ShapeRange.Fill.ForeColor.RGB = RGB(255, 239, 0)
     End Select
End With

Hi there!
I did try it quickly but it did not work, it doesn't change the graphic at all. I am just a beginner with VBA so I am trying to learn how to improve codes

 

[bebo021999]

Try, instead of
Select case Range("U5")

replace with
Select case Range("U5").Value
???

 

[drefiek2]

Try, instead of
Select case Range("U5")

replace with
Select case Range("U5").Value
???

Tried and still not working!