Answer this tonight.

[Topher]

Please Answer Tonight.

So I have a lot of text.
Rows and Rows and Rows
I have 47,300 rows which I want to assign a particular font.
The font which I would like to assign is named in another column. I have a list of 5,300 different fonts that I have installed on my computer.

I actually have their file locations listed.

Woohoo, So then.... I want to assign these awesome fonts to the sentences in column C. Is this possibly.

Once I have all of the fonts onto a particular sheet in the excel, I can use the format painter to transfer cell formatting characteristics.

If I have my font file locations listed in column F,
Maybe column E could just apply the font from the cell to it's right.
Then I would have 5,300 fonts then I would use the format painter and apply it to my text.

Okay,
But how to I write the equation for column E?????

 

[Michael M]

While a macro will do that....it begs the question.
Why do you need 5,300 fonts AND
if you load up 5,300 Fonts into a worksheet and use that workbook, I can imagine it would really struggle to cope.
and why do it when you can simply select the fonts from the dropdown font menu...although I believe that has about a 1500 limit, or at least it used to have.

 

[Topher]

Well Michael, I really don't want to use VBA because I'm just not a fan of it yet. maybe after more excel education I can implement VBA's. I've used it a few times and it's worked for me so if that is the only way to get this task accomplished, I'll use a VBA.

But to answer your question of "Why?"
I am a writer and I make magazines and publications of all sorts, postors, stickers, .jpeg's, webpages, blogs, and even eventually clothing.
So I want to have an attractive way to let people read the words.
The 5,300 fonts are obviously very vast in their varied appearance.

If the workbook doesn't work well with the 5,300 fonts, I can split it into two or more workbooks.

The drop down menu itself lags whenever I go to select a font and CTRL+D is good for selecting font characteristics but both processes are wayyyy too time consuming and tedious for me to get through all 5,300 fonts. So I know with the power of excel, I can have this task done in a matter of minutes.

I'm waiting for that magic word. voilà

So, About getting to the solution, like I said, I have all the file names and/or File Locations listen in a column.

~~~~~~~ Column F ~~~~~~~
C:\users\Topher\Desktop\CustomFonts\Zippo.ttf
C:\users\Topher\Desktop\CustomFonts\ZeroHour.ttf
C:\users\Topher\Desktop\CustomFonts\ZiggyMoe.ttf


Thanks!

 

[Michael M]

I still believe you will have to "install " the fonts in Office.
Have a look here to see how it's done

http://office.microsoft.com/en-us/excel-help/install-a-new-font-on-your-computer-HA001094742.aspx

You can't simply have a link to the fonts on your hard drive or network and click on it.

 

[Smitty]

You'll need VBA, period. A worksheet function can't invoke a physical change on a sheet.

The style limit in a workbook is 4,000, so it will need to be broken up.

And Michael is correct, you will need to have the fonts installed in order to use them.

As for this:

Please Answer Tonight.

That's a very open invitation to not gettin anything. Your post would have been answered as fast as possible just by the nature of this board.

 

[Topher]

Mike, I already have the fonts installed into Microsoft Office 2007.

You guys have any ideas on a VBA that would get this task accomplished?
I 'll have no problem splitting up into multiple workbooks.

but Smitty I am surprised that a worksheet function can't invoke a physical change on a sheet. So you mean that there's no function that could be written to change the font size, font color, cell color, Bold, Underline, or Italicize?


And Smitty, haha... about the "Please answer tonight" I sometimes just get so anxious to get to the bottom of what is holding my progress up. But thanks for the tip, your definitely right!


Thanks for any and all further help on this!

 

[Smitty]

So you mean that there's no function that could be written to change the font size, font color, cell color, Bold, Underline, or Italicize?

Not quite, Conditional Formatting can (to a certain degree).

As for the code to do it, I'd imagine you could loop through the range with the font names and use that to change the attributes of the column in question. But that could take quite some time. There might be a way to narrow down the field with autofilter/visible cells only.

 

[Michael M]

Smitty
maybe an InputBox to invoke it, but I can foresee a very slow code, if it has to find 1 of 5000 odd fonts.
However, try this after you have installed your fonts.
You will have to have a list of your fonts, or at least know the correct spelling of the font

Private Sub fonter()
Dim ans As String
ans = InputBox("What font do you want to apply ??")
    Columns("C:C").Font.Name = ans
End Sub