Extract Excel Cell Format

0
votes

I'm looking to automate the formatting process for a report.

I'm stuck on how to extract the format of a cell in excel. For example the Table Layout, Font, Color Etc.

I'm thinking of extracting the format of a cell in excel. Then copying the code and apply it to other excels that needs formatting. If there is a better solution please let me know.

2
excelformattingformat

2 Answers

2
votes

Simply copying all formats of one cell to another can be done like this:

Range("A1").Select
Selection.Copy

Range("B1").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone

Example of getting the font size:

Dim fontsize As Integer
fontsize = Worksheets("Sheet1").Cells(1, 1).Font.Size

Example of getting the font name:

Dim fontname As String
fontname = Worksheets("Sheet1").Cells(1, 1).Font.Name

Similarly you can get the font color this way as well.

To get the cell fill color:

Range("A1").FormatConditions(1).interior.color

Just Google something like this, "Excel vba get cell font name" and you'll see lot's of examples.

0
votes

If you have certain combinations of formats that you use frequently, I advise you to consider using Styles. Once defined, Styles really simplify creating and maintaining uniform formats for your cells.