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.
| Formatting | Code Example | Comment |
| Number Format | Range(“A1”).NumberFormat = “$#,##0.00” | |
| Background Color | Range(“A1”).Interior.Color = 65535 | |
| Font Name | Range(“A1”).Font.Name = “Calibri” | |
| Font Size | Range(“A1”).Font.Size = 14 | |
| Font Style | Range(“A1”).Font.FontStyle = “Italic” | Styles: Regular, Bold, Italic, Bold Italic |
| Font Underline | Range(“A1”).Font.Underline = xlUnderlineStyleDouble | Options: xlUnderlineStyleNone, xlUnderlineStyleSingle, xlUnderlineStyleDouble, xlUnderlineStyleSingleAccounting, xlUnderlineStyleDoubleAccounting |
| Font Effects | Range(“A1”).Font.Strikethrough = True | Strikethrough can be replaced with Subscript or Superscript |
| Vertical Alignment | Range(“A1”).VerticalAlignment = xlBottom | Options: xlBottom, xlCenter, xlDistributed, xlJustify, xlTop |
| Horizontal Alignment | Range(“A1”).HorizontalAlignment = xlCenter | Can be set to xlGeneral, xlCenter, xlDistributed, xlJustify, xlLeft, xlRight |
| Line Style | Range(“A1”).Borders(xlEdgeBottom).LineStyle = xlContinuous | Options: 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 Thickness | Range(“A1”).Borders(xlEdgeBottom).Weight = xlThin | Options: xlHairline (Hairline, thinnest border), xlMedium (Medium), xlThick (Thick, widest border), xlThin (Thin). |
| Line Color | Range(“A1”).Borders(xlEdgeBottom).Color = 65535 |
Relevant Articles
How Do I Combine Two Macros? (With And Without VBA)
Can VBA Be Used In Google Sheets?