How Format A Cell In VBA? A Simple Way To Find The Code?

Have you ever wondered how to find out exactly what the right code is to use for a particular type of cell formatting which could include things like the color of the background, the style of font whether it is bolded or in italic, its alignment or whether it has a border to name just a few examples? The number of different possibilities is endless so how do you do it in just one simple way?

The simplest method to help identify the correct code is to record a macro where you format the cell to your desired preferences. By doing this you’ll be able to identify the code required to format the cell in the way that you would like.

To provide an example of this I recorded a macro where I change the number format to currency. The resultant macro produced is shown below.

Now while produced is not exactly what you would use if you are writing the VBA in the VBA editor, As using the selection is something that generally should be avoided it provides you with enough information to be able to work out precisely what the command will be which is provided below.

Worksheets(“Sheets”).Range(“A1”).NumberFormat = “$#,##0.00” 

However, sometimes the information provided in a recorded macro is a little bit more convoluted and does require a little bit more interpretation example of this is when the color RSL is changed.

In this particular example, the VBA editor has produced a series of pieces of information that are not relevant but despite this, it is still possible to extract what the command is likely to be. The information in this case is split up into a couple of different areas but still has all of what is required to create the correct code. In this particular example, the correct code would be

Worksheets(“sheet1”).Range(“B10”).Interior.Color = 65535

However, even if you are not able to work out exactly how the code works at worst you can simply paste in the information above which will still work though it will have extraneous information in the code.

For your reference, I have included a table that contains the correct code in the table if you are unsure about the Syntax.

FormattingCode ExampleComment
Number FormatRange(“A1”).NumberFormat = “$#,##0.00”
Background ColorRange(“A1”).Interior.Color = 65535
Font NameRange(“A1”).Font.Name = “Calibri”
Font SizeRange(“A1”).Font.Size = 14
Font StyleRange(“A1”).Font.FontStyle = “Italic”Styles: Regular, Bold, Italic, Bold Italic
Font UnderlineRange(“A1”).Font.Underline = xlUnderlineStyleDoubleOptions: xlUnderlineStyleNone, xlUnderlineStyleSingle, xlUnderlineStyleDouble, xlUnderlineStyleSingleAccounting, xlUnderlineStyleDoubleAccounting
Font EffectsRange(“A1”).Font.Strikethrough = TrueStrikethrough can be replaced with Subscript or Superscript
Vertical Alignment Range(“A1”).VerticalAlignment = xlBottomOptions: xlBottom, xlCenter, xlDistributed, xlJustify, xlTop
Horizontal AlignmentRange(“A1”).HorizontalAlignment = xlCenterCan be set to xlGeneral, xlCenter, xlDistributed, xlJustify, xlLeft, xlRight
Line StyleRange(“A1”).Borders(xlEdgeBottom).LineStyle = xlContinuousOptions: xlContinuous (Continuous line), xlDash (Dashed line), xlDashDot (Alternating dashes and dots), xlDashDotDot (Dash followed by two dots), xlDot (Dotted line), xlDouble (Double line), xlLineStyleNone (No line), xlSlantDashDot (Slanted dashes)
Line ThicknessRange(“A1”).Borders(xlEdgeBottom).Weight = xlThinOptions: xlHairline (Hairline, thinnest border), xlMedium (Medium), xlThick (Thick, widest border), xlThin (Thin).
Line ColorRange(“A1”).Borders(xlEdgeBottom).Color = 65535

Relevant Articles

How Do I Combine Two Macros? (With And Without VBA)

Can VBA Be Used In Google Sheets?

Is Vertical Monitor Programming Worth it?

What Are The Drawbacks Of Recording Macros In Excel?

0 0 votes
Article Rating
Notify of
Inline Feedbacks
View all comments