[Excel VBA] How to Copy Only Cell Formatting | Difference Between PasteSpecial and NumberFormat

Overview

In Excel, there are many situations where you want to copy only the “Display Format” (formatting) of a cell rather than its value. This is especially useful when you want to apply fonts, colors, borders, or number formats to other cells efficiently using VBA.

In this article, I will introduce two methods to copy only the cell formatting using VBA and explain the differences between them.

Sample Code

VBA

Sub CopyCellFormatOnly()

    ' Method 1: Copy ALL formatting using PasteSpecial
    Range("F2").Copy
    Range("F5").PasteSpecial Paste:=xlPasteFormats
    
    ' Clear the clipboard (recommended after PasteSpecial)
    Application.CutCopyMode = False

    ' Method 2: Copy ONLY the number display format using the NumberFormat property
    Range("F6").NumberFormat = Range("F2").NumberFormat

End Sub

Explanation of the Code

Method 1: Copy Formatting with PasteSpecial

VBA

Range("F2").Copy
Range("F5").PasteSpecial Paste:=xlPasteFormats

• .Copy: Copies the source cell.
• .PasteSpecial Paste:=xlPasteFormats: Pastes only the formatting (Font, Borders, Fill color, Number format, etc.).
• Result: Values and formulas are not copied; only the visual appearance is applied to the target cell.

Method 2: Copy Only Display Format with NumberFormat

VBA

Range("F6").NumberFormat = Range("F2").NumberFormat

• .NumberFormat: Copies or sets only the number format string (e.g., #,##0, yyyy/mm/dd, $#,##0.00).
• Result: Fonts, background colors, and borders are not changed. Only the rule for how the number/date is displayed is reflected.

Key Differences and Usage

Application Examples

• Standardizing Number Displays: A macro to unify sales figures or amounts into a 3-digit comma-separated format across a sheet.
• Applying Templates: Applying the format of an input template to a different sheet.
• Dynamic Formatting: Copying and setting formats dynamically based on specific conditions.

Important Notes

1. Clipboard: PasteSpecial must be used immediately after .Copy.
2. Clear Clipboard: It is recommended to add Application.CutCopyMode = False after pasting to cancel the copy state (remove the marching ants border).
3. Local Formats: Using NumberFormatLocal allows you to handle locale-specific formats (such as Japanese date formats like “令和”).

Summary

In this article, I introduced two methods for copying cell formatting in VBA.

• To copy the entire look (Color, Font, etc.) $\rightarrow$ Use PasteSpecial xlPasteFormats.
• To copy only the number/date format $\rightarrow$ Use .NumberFormat.

By distinguishing between these methods based on your needs, you can perform table formatting and template creation much more efficiently.