How to copy cell formatting and style

Question

Guys if I use the following code:

dim Defaultcol As Long

Defaultcol = Sheets("Whatever").Cells(1, 1).Interior.Color

I can get the color formatting from a cell.

Could I also write something like

dim FullFormat as object (Or dim FullFormat as cellformat)

set FullFormat =  Sheets("Whatever").Cells(1, 1).formats

and get all the formats of a cell in one object?

or maybe

dim sourcecell as cell

set sourcecell = Sheets("Whatever").Cells(1, 1)

and then have property sourcecell.formats that I could use?

Thanks in advance.

Answer 1

There is a way to copy the basic formatting of a cell using MSND's Guide

While the above may be a workaround when it comes to VBA there is no direct way of copying cells Styles/Formatting.

It's true that the Style object stores the styles but no methods exist (at least I am not aware of any) which allow you to use the Style object to copy and apply formatting between cells.

There is a workaround it using VBA

One way is to use String arguments to copy from and to ie.

Private Sub CopyFormatting(fromRng As String, toRng As String)
Application.ScreenUpdating = False
    Dim tmpVal
    tmpVal = Range(toRng)
    Range(fromRng).Copy Range(toRng)
    Range(toRng) = tmpVal
Application.ScreenUpdating = True
End Sub

and Call it like

Sub Main()
    CopyFormatting "A1", "A10"
End Sub

and this will copy the formatting and retain the Value unlike lots of online guides which only copy and paste the cell

---

A better way would be to pass reference to Range objects as it allwos you to use it in between different sheets etc.

Example

Option Explicit

Sub Main()
    Dim rng1 As Range, rng2 As Range
    Set rng1 = Sheets(1).Range("B3")
    Set rng2 = Sheets(2).Range("A1")

    CopyFormatting rng1, rng2
End Sub

Private Sub CopyFormatting(fromRng As Range, toRng As Range)
Application.ScreenUpdating = False
    Dim tmpVal
    tmpVal = toRng
    fromRng.Copy toRng
    toRng = tmpVal
Application.ScreenUpdating = True
End Sub