# VBA Resize shape according to cell timevalue range



## geloader0 (Dec 21, 2022)

I want to populate my shape according to time range value in 1st Range and 2nd Range cell as shown in the image. Column A is the Range to be populated by the textbox as shown in the image. Thank you. Your help is much appreciated


----------



## John_w (Dec 21, 2022)

Welcome to MrExcel forums.

Your description, about populating the shape/range, doesn't really match your thread subject about resizing the shape.  

My understanding is that you want to resize the shape in proportion to the start and end times in F3 and G3, the cell position of the first time cell in A4 and the height of the rows.


```
Public Sub Resize_Shape()

    Dim shp As Shape
    Dim baseTimeCell As Range
    Dim startTime As Date, endTime As Date
    
    With ActiveSheet
    
        Set shp = .Shapes(1)
        Set baseTimeCell = .Range("A4")
        startTime = .Range("F3").Value
        endTime = .Range("G3").Value
       
        'Top of shape
        
        shp.Top = baseTimeCell.Top + (baseTimeCell.Height / 60) * DateDiff("n", baseTimeCell.Value, startTime)
        
        'Bottom of shape
        
        shp.Height = baseTimeCell.Top + (baseTimeCell.Height / 60) * DateDiff("n", baseTimeCell.Value, endTime) - shp.Top
        
    End With
    
End Sub
```


----------



## geloader0 (Dec 22, 2022)

Hello John_w *Good day, This is exactly what I am looking for. Sorry about for misinformation about my thread because this is my first time to post a thread. Thank you so much again.*​


----------

