anjulis
anjulis

Reputation: 229

Save all information from a range and restore it later

Is there a way to assign a font to a range?

Let's say I have an object myFont. I can write:

 with Range("A1").Font
       .Bold=myFont.Bold
       .Size=myFont.Size
       same with other properties
end with

but there are a lot of font properties. Is there a way to do something like Range("A1").Font=myFont?

Upvotes: 0

Views: 194

Answers (2)

Tim Williams
Tim Williams

Reputation: 166511

No one-liner to do what you want. One possible shortcut below, but you're really still iterating over each property...

Dim p, myFont, rng As Range

'populate myFont, rng

For Each p In Array("Bold", "Color", "Size") 'for example
    CallByName rng.Font, p, VbLet, CallByName(myFont, p, VbGet)
Next p

Upvotes: 1

DevGin
DevGin

Reputation: 453

To change the font properties, follow the code below. Specifically, to change Range("A1") font name, use "Font.Name" as shown below.

To get this, I simply recorded a Macro of me changing the font and then stopping the Macro to view the code. Try recording Macros to find the property you need at times. Hope this helps.

Sub ChangeFontCustomRange()

' Change Font Name

    'Select the Range to change font to
    Range("A1").Select

    'Change font properties, specifically, Font.Name
    With Selection.Font
        .Name = "Calibri" 'Type exact font name here
        .Size = 11
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
End Sub

Upvotes: 0

Related Questions