Add drop shadow to cells

[Guest]

I am using a recorded a macro that loops through a series of sheets and adds a shadow effect to a cluster of cells, say B5:G8 and it works fine. This code follows:

ActiveSheet.Shapes.AddShape(msoShapeRectangle, 1.4, 40.6, 568.8, 51.3).Select
Selection.ShapeRange.Fill.Visible = msoFalse
Selection.ShapeRange.Shadow.Obscured = msoTrue
Selection.ShapeRange.Shadow.Type = msoShadow14

The problem is, if the column widths change from one sheet to the next (and they do) then the drop shadow is out of line on subsequent sheets.

How can I get the drop shadow to apply to a range address rather than the sheet coordinates (pixels?) recorded?

Your example code would be most helpful. Thanks in advance.

This only needs to run once per sheet.

 

[Ron de Bruin]

Maybe this

ActiveSheet.Shapes.AddShape(msoShapeRectangle, Range("B5").Left, Range("B5").Top, _
Range("B5:g8").Width, Range("B5:b8").Height).Select
Selection.ShapeRange.Fill.Visible = msoFalse
Selection.ShapeRange.Shadow.Obscured = msoTrue
Selection.ShapeRange.Shadow.Type = msoShadow14


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)

I am using a recorded a macro that loops through a series of sheets and adds a shadow effect to a cluster of cells, say B5:G8

and it works fine. This code follows:

 

[Guest]

Thanks!