louww
02-28-2011, 08:56 AM
Any help would be appreciated.
I would like to create a color heatmap with shapes. The shapes should be linked to a cell value and a certain color according to value (1,2,3,4,5)
I've tried to adapt p45calc's code with no luck:
Private Sub Worksheet_Change(ByVal Target As range)
With Target
If IsNumeric(.Value) And .Value > 1 <= 2 Then
Set TheCircle = ActiveSheet.Shapes.AddShape(msoShapeOval, _
.Left, _
Top:=.Top, _
Width:=.Width, _
Height:=.Height)
' Optional stuff:
With TheCircle
LockAspectRatio = msoTrue
With .Line
Weight = 3.5
.ForeColor.SchemeColor = 10
.BackColor.RGB = RGB(255, 255, 255)
End With
With .Fill
.Solid
.ForeColor.SchemeColor = 43
.Transparency = 0.69
End With
End With
End If
End With
End Sub
I would like to create a color heatmap with shapes. The shapes should be linked to a cell value and a certain color according to value (1,2,3,4,5)
I've tried to adapt p45calc's code with no luck:
Private Sub Worksheet_Change(ByVal Target As range)
With Target
If IsNumeric(.Value) And .Value > 1 <= 2 Then
Set TheCircle = ActiveSheet.Shapes.AddShape(msoShapeOval, _
.Left, _
Top:=.Top, _
Width:=.Width, _
Height:=.Height)
' Optional stuff:
With TheCircle
LockAspectRatio = msoTrue
With .Line
Weight = 3.5
.ForeColor.SchemeColor = 10
.BackColor.RGB = RGB(255, 255, 255)
End With
With .Fill
.Solid
.ForeColor.SchemeColor = 43
.Transparency = 0.69
End With
End With
End If
End With
End Sub