a shape (rectangle) will be based on two cells
Both shapes
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
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
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | Start | End | Oct 23 | Oct 24 | Oct 25 | Oct 26 | Oct 27 | Oct 28 | Oct 29 | Oct 30 | Oct 31 | Nov 01 | Nov 02 | ||
2 | Oct 23 | Oct 29 |
Sheet: Sheet1 |