Creating Shapes with text and certain colours all from a range VBA

[Luke1690]

Hi All ,

I have had ago at this myself and got as far as inserting 1 shape from a button. Fair to say my skills are limited.....

I'm looking for a way to create a number of shapes with texts inside them depending on a filtered range. (bonus if you could change the colour of each shape deepening on a value as well)

i want the macro to create shapes once the filtered range has been selected

RANGE F41 : P41 (F41 Is where the filtered range formula is)

The number of rows will change depending on selection.

The text i want to show in the shape is in the F Column from F41 down depending on filter.

The shape is always a circle, size Call addshape(Cells(10, 10), 70)

Text size anything that fits, text being inserted will only be 1 word.

Thanks for your help all, much a appreciated i know this is a big task.

 

[Luke1690]

Update****

Sub addshapes()
Dim x As Integer, y As Integer, w As Integer, h As Integer
Dim shp As Shape, cell As Range

For Each cell In Selection

x = cell.Left - 30
y = cell.Top + 30
w = cell.Width - 30
h = cell.Height + 30

Set shp = ActiveSheet.Shapes.addshape(msoShapeFlowchartConnector, x, y, w, h)
With shp.TextFrame
.Characters.Text = cell.Value
.Characters.Font.Bold = True
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter

End With

Next cell
End Sub

so this works a treat for what i need.

is there a way to change the colour of these shapes depending on a cell value?

also a better way to run the code rather than selecting the range?


Thanks guys