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.

 

[My Aswer Is This]

Try this:
Right click cell
Choose Format Cell
Choose Fill
Choose Fill Effect

See if that does what you want.

If you want Vba code to do this:
Use the Macro recorder

 

[My Aswer Is This]

If you want a macro to do the same formatting in a range of cells.

Here is what I did.

Now this color may not be exactly what you want.

So you may want to use the Macro Recorder on one cell. Or if the range will always be the same

Select the range and then do the steps I said earlier

My script here using the selection which is what ever selection you choose.

I ran the recorder modified the script and just used selection not specifying what selection.

So now what ever selection I select will be formatted the way the script says.

Sub Color_Me()
'Modified  1/24/2019  4:31:51 AM  EST
    
    With Selection.Interior
        .Pattern = xlPatternLinearGradient
        .Gradient.Degree = 270
        .Gradient.ColorStops.Clear
    End With
    With Selection.Interior.Gradient.ColorStops.Add(0)
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    With Selection.Interior.Gradient.ColorStops.Add(1)
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = 0
    End With
End Sub

 

[peten]

Try this:
Right click cell
Choose Format Cell
Choose Fill
Choose Fill Effect

See if that does what you want./QUOTE]

This creates a gradient fill, not a shadow. Anyway, macro recording yields:

With Selection.Interior
.Pattern = xlPatternLinearGradient
.Gradient.Degree = 90
.Gradient.ColorStops.Clear
End With

Tried to apply this to the shadow, but this did not work:

With Selection.Interior 'also tried without .Interior
.Shadow.Type = msoShadow22
End With

 

[peten]

Actually, if the cell has conditional formatting, the gradient fill will not work while shadow still produces the effect (since it is not in the cell itself but in a shape placed over the cell).

 

[My Aswer Is This]

I just gave a suggestion.

You can play with the recorder.

Cells probable cannot be formatted the same as shapes.
But maybe someone else here on Mr. Excel will have another answer.

 

[peten]

Since there was no luck with the recorded, I tried to do this the hard way, i.e. creating a rectangle over each cell using VBA. Another strange difficulty arose: it does not seem to be possible to create a similar rectangle manually as the one added by excel. Please see the comparison:

The rectangle around 0.64 you can add e.g. typing "shadow" in the "Tell me what you want to do" box and select the first outer shadow under Shape Effects. Then go to format menu and change Shape outline to none.

But try to do the same yourself by adding a rectangle, then in format menu add shadow, remove shape fill and outline, i.e. the same settings you can see in the excel generated rectangle. I left the outline in the rectangle around 0.82, if you remove it, your rectangle becomes just a transparent box. Even with the outline it does not look the same.

Please let me know if I'm missing something obvious...

 

[mikerickson]

If you make the interior fill of the rectangle white, rather than no-fill, and then remove the border, the image should be like the other

 

[peten]

If you make the interior fill of the rectangle white, rather than no-fill, and then remove the border, the image should be like the other

If you do that, the image looks the same but you can no longer see the number underneath (white rectangle is is no longer transparent).

 

[mikerickson]

You can put the number in as the text for the rectangle.