Adding a shadow for a cell in VBA (macro recording fails)

[peten]

I try to add a shadow in a cell (Format toolbar - Shape effects - shadow). However, if I try to do the same in macro recording, the recording is blank. When one clicks the Shape effects - shado, Excel actually creates a rectangle just the size of the cell and gives it a shadow; somehow the recording fails to capture this.

If I create a random shape and run the macro, the relevant lines will be:
Selection.ShapeRange.Line.Visible = msoFalse
Selection.ShapeRange.Shadow.Type = msoShadow22

But how can I make a VBA to apply these to cells (not to a shape)? I'd like to loop through a range and repeat this for each cell.

 

[mikerickson]

Try this after adjusting the values for oneShape and oneCell.

Sub test()
    Dim oneShape As Shape
    Dim oneCell As Range
    Set oneShape = ActiveSheet.Shapes(1)
    Set oneCell = Range("Q20")
    
    With oneShape
        .Fill.ForeColor.RGB = oneCell.DisplayFormat.Interior.Color
        .Top = oneCell.Top
        .Left = oneCell.Left
        .Height = oneCell.Height
        .Width = oneCell.Width
        With .TextFrame
            With .Characters
                With .Font
                    .Color = oneCell.Font.Color
                    .Name = oneCell.Font.Name
                    .Size = oneCell.Font.Size
                End With
                .Text = "cat"
            End With
        End With
        With .TextFrame2
            .VerticalAnchor = msoAnchorMiddle
        End With
    End With
End Sub

 

[peten]

Thank you! It indeed looks like the same rectangle cannot be created manually to be transparent.The problem is that in addition to numbers, the underlying cells also have colours from conditional formatting. I can use your suggestion plus loop through all the cells, copying their values to rectangles. It looks like I would also need to recreate the conditional formatting in VBA.

The first time I encountered a formatting done by Excel which cannot be replicated manually...

EDIT: actually your code also preserves the colour from the conditional formatting which is great!