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.
Step 1
Open VB editor from Developer tab in excel as shown in the figure.
Step 2
Insert a module in which we will write a VBA code to get the font details from excel cell
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
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.
Hope this helped.
