I am looking for a way to list all the fonts that are used in an entire workbook. Initially I just need to know what fonts are used throughout, but if this code could also be used to list what worksheet each font is used that would also be great.
I am looking for a way to list all the fonts that are used in an entire workbook. Initially I just need to know what fonts are used throughout, but if this code could also be used to list what worksheet each font is used that would also be great.
See attached. About the only thing I could find in short order.
HTH
Change references as required.
It does not use Sheet1 and it enters the info in Columns B, C and D in Sheet1
If you have a lot of sheets/data it will probably be slow.
Sub Get_All_Fonts() Dim i As Long, c As Range, sh1 As Worksheet Set sh1 = Sheets("Sheet1") Application.ScreenUpdating = False sh1.Range("D1").Value = sh1.Range("D1").Font.Name For i = 2 To ActiveWorkbook.Sheets.Count With Sheets(i) For Each c In .Range("A1").CurrentRegion If Application.WorksheetFunction.CountIf(sh1.Range("D1:D" & sh1.Cells(Rows.Count, 4).End(xlUp).Row), c.Font.Name) = 0 Then sh1.Cells(Rows.Count, 4).End(xlUp).Offset(1, -2).Value = Sheets(i).Name sh1.Cells(Rows.Count, 4).End(xlUp).Offset(1, -1).Value = c.Address sh1.Cells(Rows.Count, 4).End(xlUp).Offset(1).Value = c.Font.Name End If Next c End With Next i Application.ScreenUpdating = True End Sub
It took me a while to make some corrections to my 30 page workbook that would allow this code to run fully through all worksheets but it does seem to work well with the only problems I experienced being the particular way my worksheets are designed. Thank you for providing this.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks