Excel formatting with VBA

Education
,
1

Hi all,

I want to copy a formatting of a cell using VBA and I’ve seen that you need to copy each “piece” of a format, which is very inconvenient when you have all borders added for the cell. Is there any way around this problem? Or is it possible to define styles in VBA? Can you maybe share some training materials?

2

Hi @Anna_Wieczorek,
from what I’ve read I see that this a problem for a lot of people. You can define styles, but you need to define each parameter of this style one by one.

With ActiveWorkbook.Styles.Add("Style_test")
   .Font.Bold = True
   .Font.Size = 12
   .Font.Color = vbRed
   .Borders.LineStyle =xlContinous
   .Borders.Weight = xlThick
   .Borders(xlDiagonalDown).LineStyle = xlLineStyleNone
   .Borders(xlDiagonalUp).LineStyle = xlLineStyleNone
   .HorizontalAlignment = xlHAlignCenter
End With

And the biggest problem with this approach are borders, which also have to be defined one by one. There is a way to make it slighty better, but it’s also not perfect. You can copy the whole format from one cell to another with a makro like this:

Private Sub CopyFormatting(from_rng As Range, to_rng As Range)
Application.ScreenUpdating = False
    Dim tmp_val
    tmp_val = to_rng.Value
    from_rng.Copy to_rng
    to_rng.Value = tmp_val
Application.ScreenUpdating = True
End Sub

It copies one cell and paste it chosen place and then pastes the old value, so it’s not overwritten. The downside of this solution is that if you need to paste formats to multiple cells, over different worksheets this macro “selects” those cells and it causes the screen to flicker, that’s why we have to disable screen updating for this macro.

You can also manually create style in Excel, and then VBA can easily use this style like this:

Range("A1").Style = "Style_test"

So there are some ways but none of them is perfect and I would love to see a better method of handling styles in VBA :confused:

3

Good Day My Friends,

I would like to add one thing to what @Mateusz_Zemojdzin did in CopyFormatting macro. It is not necessary to copy values, we can use more ‘excel’ approach with that and do

from_rng.Copy
to.rng.PasteSpecial xlPasteFormats
Application.CutCopyMode = False

This works similarly to Format Painter function in excel.
It also possible to create similar macro, which would set the format parameters in “to_rng” based on format parameters in “from_rng”. The obvious con is that it is necessary to define all those parameters in macro. The pro is that we don’t have to use .Copy anymore.

4

Hi Tanuki,
I’ve checked your code and unfortunately the first approach also causes screen to flicker. Of course we can disable screen updating, but I’m curious if there is a way to copy and paste format without the need to disable screen updating.

The other solution is similar to creating custom style. If you need to copy formats several times it’s useful to have style just for this.

5

Good Day Arnalt,

Avoiding turning off screen updating might be hard, with large number of format changes it might happen whatever we do, so unfortunately I can’t help with that.

As for second point, You are correct. The solution is basically the same as with creating new style, but without creating style. It might be useful in case You want to copy various formats without need to create style for each. Additionally, it gives control of what parameters from format we would like to copy. This obviously does not help the initial issue as we need to define all parameters we want to copy in macro.
Reusing codes created by @Mateusz_Zemojdzin this is how it could look like:

Private Sub CopyFormatting(from_rng, to_rng)

Dim singlecell As Range

For Each singlecell In to_rng
    With singlecell
        .Font.Size = from_rng.Font.Size
        .Font.Bold = from_rng.Font.Bold
        .Font.Color = from_rng.Font.Color
        .Borders.LineStyle = from_rng.Borders.LineStyle
        .Borders.Weight = from_rng.Borders.Weight
        .Borders(xlDiagonalDown).LineStyle = from_rng.Borders(xlDiagonalDown).LineStyle
        .Borders(xlDiagonalUp).LineStyle = from_rng.Borders(xlDiagonalUp).LineStyle
        .HorizontalAlignment = from_rng.HorizontalAlignmen
        .Interior.Color = from_rng.Interior.Color
    End With
Next singlecell

End Sub