Return Font Name And Font Size In VBA

In this post we will see how to return font size and font name for the values in excel cell.

We will write a vba program that will loop through the entire data in column A and find the value of font name and font size for each of the cell values in a sheet.

We may come across some situation like this where we need to find format details for the cell in order to format remaining data inn that sheet or in that particular row.




Below is the data in column A and format details in column B tat we would want to extract, so we have the font name and font size in column B(font name and font size is separated by a colon) for each of the corresponding values in column A.

Excel showing data with different font names and font size

Step 1

Open VB editor from Developer tab in excel as shown in the figure.

Open developer tab to write VBA to return font name and font size

Step 2

Insert a module in which we will write a VBA code to get the font details from excel cell

Insert a module in the VB editor

Step 3




Now double click the newly created module to open that and paste the below code in the module.

Public Sub GetFormatDetails() 

Dim CurrentSheet As Integer

    Dim UsedRange As Range

    Dim CurrentCell As Range

    Dim rw As Long

    rw = 1

        Set UsedRange = Range(Sheets(ActiveSheet.Index).Range(“A1”), Sheets(ActiveSheet.Index).Range(“A1”).SpecialCells(xlLastCell))

        For Each CurrentCell In UsedRange

            FontUsed = CurrentCell.Font.Name + “:” + CStr(CurrentCell.Font.Size)

            If Sheets(“Sheet1”).Cells.Find(FontUsed) Is Nothing Then

                Sheets(“Sheet1”).Cells(rw, 2).Value = FontUsed

                rw = rw + 1

            End If

        Next

End Sub

VBA code returning font types and font size

Now run the code by pressing F5 or by clicking on play button in the editor to get the font details from excel cell.

You can see that we have got the font details in the result.

Result showing we have got the font names and font font size inn column B

Hope this helped.




Share The Knowledge

Random Posts