Recorded Macro Fails On Error'438'

[homeric]

I am trying to create a macro or vba to get the same effect that I get through manually formatting my worksheet – The manual method allows me to select a specific part row apply the formatting then clear the formatting after saving or printing the sheet, I would then go through the whole process again on the next row I wanted to format

My manual method is to: select the part row (Covering Cols B to M) > Format > Shape Effects > Shadow > Select an option > Right Click Border > Format Shape > Effects > Shadow > Choose Color > Set Transparency to 10% > Blur 30pt > Select All Cells A1 to N21 > Save or Print (A1 to M19) > Then several undos > Start on another row…

When I try to record a macro of it I get this error:-

Run-time error '438'; Object doesn't support this property or method

Debug then shows:

Sub BorderShadowEffeet()

BorderShadowEffect Macro

Macro to put shadow border effect around pre-selected row

Keyboard Shortcut: Ctrl+Shift+Q

With Selection.ShapeRange.Shadow

.Type = msoShadow25

.Visible = msoTrue

.Style = msoShadowStyleOuterShadow

.Blur = 5 .OffsetX = 0 .OffsetY = 0

---And more lines shown, but doesn't get that far---

My attempts to create a vba to do this have just confused me even more – Can anyone help me please?

The final output I want, and which I can get manually, is like this:-

 

[rlv01]

  With Selection.ShapeRange.Shadow

Unless you have a shape object selected when you run the macro you are always going to get that error.

Some thoughts: Having a fair bit of experience with the macro recorder, the code you posted looks too "clean", like you tided it up and maybe removed a crucial element in the process. Such as the part where it creates a rectangle shape and selects it. Example:

Sub ShadowRange()
    
    Dim Sh As Shape
    Dim R As Range
    
    Set R = Range("B8:M8")
    Set Sh = ActiveSheet.Shapes.AddShape(msoShapeRectangle, R.Left, R.Top, R.Width, R.Height)
    With Sh
        .Fill.Visible = msoFalse
        .Line.Visible = msoTrue
    End With
    
    Sh.Select
    Call BorderShadowEffect
End Sub

 

[RoryA]

If you recorded the macro after selecting the shape, it recorded on the basis that you have a shape selected. So if you don't, the macro will fail when you run it.