Drawing rectangles from within a macro

Posted by Kadir Haldenbilen on December 11, 2001 3:44 AM

I can draw rectangles from within a macro OK. But I need the rectangles to have exact fit on given range of cells. Therefore,I need a command format with cell references like TopLeftCell, BottomRightCell; instead of x, y, w, h format.

Posted by Colo on December 11, 2001 4:12 AM

Hi, Kadir.
Here's a sample I made for you.
Try this!

Sub Test()
Dim rng As Range, MyRct As Shape
Set rng = Application.InputBox("Please Select Range", Type:=8)
With rng
Set MyRct = ActiveSheet.Shapes.AddShape(1, .Left, .Top, .Width, .Height)
End With
End Sub

Posted by Kadir Haldenbilen on December 11, 2001 6:55 AM

Posted by Kadir Haldenbilen on December 11, 2001 6:57 AM

Thanks Colo,

How should I modify this macro if I am extracting range info as row, column inexes?

Posted by Kadir Haldenbilen on December 11, 2001 7:01 AM

Thanks Colo,

How should I modify this macro if I am extracting range info as row, column indexes?

Posted by Kadir on December 11, 2001 7:37 AM

OK. I managed to do it using:
Set rng = Range(Cells(i, j), Cells(n, m))

Now I need to color it. Working on it.

Posted by Juan Pablo G. on December 11, 2001 7:46 AM

Use the Interior.ColorIndex property (NT)

Posted by Colo on December 11, 2001 8:23 AM

Juan! Thank you for your help.

Hi Kadir.

Sub Test2()
Dim rng As Range, MyRct As Shape
Dim i As Integer, j As Integer, n As Integer, m As Integer
i = 1: j = 1: n = 5: m = 5
Set rng = Range(Cells(i, j), Cells(n, m))
With rng
Set MyRct = ActiveSheet.Shapes.AddShape(1, .Left, .Top, .Width, .Height)
'Like This...Change Index you want
MyRct.Fill.ForeColor.SchemeColor = 12
MyRct.Line.ForeColor.SchemeColor = 23
End With
End Sub