Excel VBA Help – Assign Font from Adjacent Cell

[SWAT One]

I have a lot of fonts installed. We're talking about in the 1,600 to 1,700 range. I like to make all kinds of neat titles and headers out of them as a hobby, partially for the larger projects that I do within Forum Games. As you could imagine, finding the right font within over a thousand fonts is not easy, and sometimes you need to see the font to know what it looks like. I am categorizing each font, including by Serif/Sans-Serif, style, such as calligraphic, pictographic, distorted, digital, etc., and I will be making a separate sheet with search tools where I can narrow down results of this giant list by name, category, etc. I will return to this later.

Originally, I created a long list of fonts by name in an Excel spreadsheet in Column A with the font name being displayed larger and in the font specified in Column A. Problem is, Excel isn't too keen on having somewhere over 800 fonts running at once.

Naturally I want to have the search functions to return all fonts that satisfy the conditions in this sheet's Column A, but I want to do more than just display the font name in the generic font. I want to be able to replicate the data in Column A in Column B while setting it to the respective font, 48pt.

Is here someone who would be willing to write a function (yes, in VBA; that's what Microsoft Excel uses) on this and let me know how to implement it? I would be very thankful for your help.

[Ad Bot]

[Foxscotch]

you would probably be better off using some dedicated software. it's not really the kind of thing excel is suited to
https://en.wikipedia.org/wiki/Font_management_software#List_of_font_management_software
MainType looks nice and there's a free version that still does anything you'd probably need (and certainly more than the worksheet would)

but if you are really intent on using excel then I guess good luck

[SWAT One]

Also sorry to be a killjoy, but I'm running MacOSX so any Windows programs will not work. Sorry. :/

[Darryl McKoy]

Alt+F11 to get to the vba editor, and in 'ThisWorkbook', add the code below:

Code: [Select]

Sub AssignFont()
    Dim rng As Range
   
    For Each rng In Selection
        rng.Offset(0, 1).Value = rng.Value
        rng.Offset(0, 1).Font.Name = Trim(rng.Value)
        rng.Offset(0, 1).Font.Size = 48
    Next
   
End Sub

Select the range of cells with font names in column A, and go to the 'Developer' tab of the menu bar > Macros > Run

It shouldn't error if the font doesn't exist, it will just display it in whatever the generic font is.

[Pecon]

Also sorry to be a killjoy, but I'm running MacOSX so any Windows programs will not work. Sorry. :/

If you took like a few seconds to look at the list they linked, you'd notice there are countless cross-platform options. Most of these will probably be much better options than using a spreadsheet.