Change shape size (Rectangle) based on two cell values (Start Date) & (End Date) Excel 2013

[krllb]

Hi Excel Guru's,


Im trying to create a scheduler where in a shape (rectangle) will be based on two cells (Start Date) and (End Date).

Both shapes and date values are on the same sheet.

Can you help me? Advance thanks to you all.

 

[Yongle]

Which cell is Start Date ?
Which cell is End Date ?
Are they named ranges?
Are they always the same cells?


- how many shapes are there ?
- your post is not consistent

a shape (rectangle) will be based on two cells

Both shapes

One way ..

Sub Size()
    Dim shp As Shape, cel As Range
    
    With ActiveSheet
        Set shp = .Shapes("Rectangle 1")
        Set cel = .Range("B2")
    End With
    With shp
        .Height = cel.Height
        .Width = cel.Width
    End With

End Sub

 

[krllb]

Hi Yongle,

Thank you for your response.

For now I am starting with just 1 rectangular shape.

Which cell is Start Date ? B2
Which cell is End Date ? C2
Are they named ranges? No
Are they always the same cells? Yes.

I am trying to create a scheduler where in I can display the schedule of an item (ex. Program Development Phase) based from its start date and end date.

_________A___________________B_______C_____D____E_____F______G____H_____I____J______K_____L
1 Program Development Phase Start Date End Date Oct 23 Oct 24 Oct 25 Oct 26 Oct 27 Oct 28 Oct 29 Oct 30 Oct 31
2 ________________________Oct 23____Oct 29 --------------------------------------------------->
3
4

I want it to be dynamic so when I change the date it would reflect on the Shape.
Apologies, im having a hard time aligning the columns.

Hope you could help. Thanks!

 

[Yongle]

I am having a hard time understanding precisely what you want
What exactly do you mean by "reflect on the Shape" ?

your shape is named "rectangle 1"
B2 = Oct 23
C2 = Oct 29

Are you wanting "rectangle 1" to be resized so that it stretches from D2 to J2 ?

 

[Yongle]

see post#4

1. run from sheet containing shape and values
2. The values in row 1 and those in B2 and C2 are DATES (not text)

Sub Resize()
    Dim L As Range, R As Range
    Set L = Rows(1).Find(Range("B2")).Offset(1)
    Set R = Rows(1).Find(Range("C2")).Offset(1)
    With ActiveSheet.Shapes("Rectangle 1")
        .Left = L.Left
        .Top = L.Top
        .Width = Range(L, R).Width
        .Height = L.Height
    End With
End Sub

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][th]

H​

[/th][th]

I​

[/th][th]

J​

[/th][th]

K​

[/th][th]

L​

[/th][th]

M​

[/th][th]

N​

[/th][th]

O​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td][/td][td]Start[/td][td]End[/td][td]

Oct 23​

[/td][td]

Oct 24​

[/td][td]

Oct 25​

[/td][td]

Oct 26​

[/td][td]

Oct 27​

[/td][td]

Oct 28​

[/td][td]

Oct 29​

[/td][td]

Oct 30​

[/td][td]

Oct 31​

[/td][td]

Nov 01​

[/td][td]

Nov 02​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td][/td][td]

Oct 23​

[/td][td]

Oct 29​

[/td][td=bgcolor:#000000][/td][td=bgcolor:#000000][/td][td=bgcolor:#000000][/td][td=bgcolor:#000000][/td][td=bgcolor:#000000][/td][td=bgcolor:#000000][/td][td=bgcolor:#000000][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]