Smart Macro required!

[bish01]

Apologies for this.... Can someone write a macro for me that will make a circle or icon change it's size and colour depending on the cell data size (e.g. 16 = small, 32 = med etc.)I would be grateful.

 

[Colo]

Welcome bish01,

To write a macro, we need to know more details what you want.
Following code is just sample, so try it and let us have your comment.

'Put this code on ThisWorkbook Module
<PRE>
Private Sub<FONT color=red> Workbook</FONT>_Open()

<FONT color=red>With </FONT>ActiveSheet

<FONT color=red>With </FONT>.[A1:E1]

.Value = Array("Top", "Left", "Width", "Height", "ForeColor")

.Font.Bold =<FONT color=red> True</FONT>

.Offset(1).Value = Array(100, 100, 100, 100, 12)

.AutoFormat Format:=xlRangeAutoFormatLocalFormat4

.EntireColumn.AutoFit

<FONT color=red>End With</FONT>

<FONT color=red>End With</FONT>

ActiveWindow.DisplayGridlines =<FONT color=red> False</FONT>

<FONT color=red>End Sub</FONT>
</PRE>

'Put this code on Sheet Module
<PRE>


<FONT color=red>Private <FONT color=red>Sub </FONT></FONT>Worksheet_Change(<FONT color=red>ByVal</FONT> Target <FONT color=red>As</FONT> Excel.Range)

<FONT color=red>Const </FONT>strShpName <FONT color=red>As</FONT><FONT color=red> String</FONT> = "MyCircle"

<FONT color=red>Dim </FONT>shpCircle <FONT color=red>As</FONT> Shape

<FONT color=red>Dim </FONT>sngX <FONT color=red>As</FONT><FONT color=red> Single</FONT>

<FONT color=red>If </FONT>Target.Count > 1<FONT color=red> Then </FONT><FONT color=red>GoTo</FONT> Terminate

<FONT color=red>If </FONT>Not IsNumeric(Target.Value)<FONT color=red> Then </FONT><FONT color=red>GoTo</FONT> Terminate



sngX = Target.Value



<FONT color=red>On Error</FONT> <FONT color=red>Resume </FONT>Next

<FONT color=red>Set </FONT>shpCircle = ActiveSheet.Shapes(strShpName)

<FONT color=red>If </FONT>shpCircle Is<FONT color=red> Nothing</FONT> Then

<FONT color=red>Set </FONT>shpCircle = ActiveSheet.Shapes.AddShape(msoShapeOval, 50, 50, 50, 50)

shpCircle.Name = strShpName

<FONT color=red>End If</FONT>

Err.Clear

<FONT color=red>On Error</FONT> <FONT color=red>GoTo</FONT> 0



<FONT color=red>With </FONT>shpCircle

Select <FONT color=red>Case </FONT>Target.Address(0, 0)

<FONT color=red>Case </FONT>"A2" <FONT color=#339966>'Top
</FONT>
.Top = sngX

<FONT color=red>Case </FONT>"B2" <FONT color=#339966>'Left
</FONT>
.Left = sngX

<FONT color=red>Case </FONT>"C2" <FONT color=#339966>'Width
</FONT>
.Width = sngX

<FONT color=red>Case </FONT>"D2" <FONT color=#339966>'Height
</FONT>
.Height = sngX

<FONT color=red>Case </FONT>"E2" <FONT color=#339966>'ForeColor
</FONT>
<FONT color=red>On Error</FONT> <FONT color=red>Resume </FONT>Next

.Fill.ForeColor.SchemeColor = CLng(sngX)

<FONT color=red>If </FONT>Not Err.Number = 0<FONT color=red> Then </FONT>MsgBox Err.Description

Err.Clear

<FONT color=red>End Select</FONT>

<FONT color=red>End With</FONT>

Terminate:

<FONT color=red>Set </FONT>shpCircle =<FONT color=red> Nothing</FONT>

<FONT color=red>End Sub</FONT>




</PRE>