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

peten

New Member
Joined
Jan 24, 2019
Messages
7
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

Well-known Member
Joined
Jul 5, 2014
Messages
16,272
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
 
Last edited:

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,272
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.
Code:
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

New Member
Joined
Jan 24, 2019
Messages
7
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

New Member
Joined
Jan 24, 2019
Messages
7
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

Well-known Member
Joined
Jul 5, 2014
Messages
16,272
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

New Member
Joined
Jan 24, 2019
Messages
7
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
22,784
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

New Member
Joined
Jan 24, 2019
Messages
7
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).
 

Forum statistics

Threads
1,081,747
Messages
5,361,038
Members
400,610
Latest member
ebey

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top