colour an picture based on a value

[robjowens]

Had a google and tried to use the recorder but haven't been able to find anything appropriate...

Any ideas on how I would be able to alter the colour of an picture based on a value in the cell, i.e. if cell B7 is between 1-10 it's blue, 11-20 green, 21+ yellow...

Any tips or reference points would be greatly appreciated.

cheers,
rob

 

[OfficeUser]

Hello Rob,

I tried to insert a picture and color it but it barely did anything. I used a shape instead. Place this code in the worksheet module for which it will be used.

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("B7").Value = 0 Then
ActiveSheet.Shapes("Oval 1").Fill.ForeColor.SchemeColor = 1
Else
If Range("B7").Value = "" Then
ActiveSheet.Shapes("Oval 1").Fill.ForeColor.SchemeColor = 1
Else
If Range("B7").Value > 0 And Range("B7") < 11 Then
ActiveSheet.Shapes("Oval 1").Fill.ForeColor.SchemeColor = 48
Else
If Range("B7").Value > 10 And Range("B7") < 21 Then
ActiveSheet.Shapes("Oval 1").Fill.ForeColor.SchemeColor = 11
Else
If Range("B7").Value >= 21 Then
ActiveSheet.Shapes("Oval 1").Fill.ForeColor.SchemeColor = 13
End If
End If
End If
End If
End If
End Sub

If B7 is blank or 0 the shape will be white. You type in a number, move to another cell and the color should change.

If you know that the cell will always be 1 or greater you can use this instead:

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("B7").Value > 0 And Range("B7") < 11 Then
ActiveSheet.Shapes("Oval 1").Fill.ForeColor.SchemeColor = 48
Else
If Range("B7").Value > 10 And Range("B7") < 21 Then
ActiveSheet.Shapes("Oval 1").Fill.ForeColor.SchemeColor = 11
Else
If Range("B7").Value >= 21 Then
ActiveSheet.Shapes("Oval 1").Fill.ForeColor.SchemeColor = 13
End If
End If
End If
End Sub