Auto Resizing Shape between specified cells Rows & Columns VBA

[Hayf13]

Hey Gang! Hope all is well, hoping for a little help - had a macro set up to generate some signs and autosize in a set pattern which worked perfectly on my laptop - however noticed when moving to a different screen ratio this didn't return the results I was hoping for - so wondering if it's possible to automatically resize the selected shape between two distinct cells (In my case to the upper left of cell U4 &Lower right of cell Z13)

' testphoto Macro
'

Selection.ShapeRange.Name = "displayphoto"
Selection.Name = "displayphoto"

'FIND WAY TO RESIZE/FIT SHAPE BETWEEN UPPER LEFT OF U4 AND LOWER RIGHT OF Z13
'MAYBE Selection.ShapeRange.ScaleWidth ??
' Selection.ShapeRange.ScaleHeight ??


Selection.ShapeRange.LockAspectRatio = msoFalse

Application.CommandBars("Format Object").Visible = False

Call pricesigncreator


What was working fine on laptop was when I had defined pixel heights etc - any help would be marvellous!

Cheers all

 

[Hayf13]

I did it! Posting the answer here incase it helps someone else - was as simple as defining the width/lengths as ranges.....days that took. D'oh

MyRange = ("U4:Z13")
Selection.ShapeRange.Name = "displayphoto"
Selection.Name = "displayphoto"


With Selection.ShapeRange
.LockAspectRatio = msoFalse
.Width = Range(MyRange).Width
.Height = Range(MyRange).Height


With Selection
.Placement = xlMoveAndSize
.PrintObject = True
End With



Call pricesigncreator