In this tutorial we’ll learn how to use Visual Basic for Applications (VBA) to modify text size and style in an Excel cell based on the cell content.
Before you start coding, you should enable your developer tab on Excel in the Ribbon, as otherwise you won’t be able to access your Visual Basic Editor.
Change your Excel cell text properties with VBA
Define your spreadsheet
We’ll start by defining an Excel spreadsheet that we will use as an example. Feel free to use it to follow along this tutorial.
- Open Microsoft Excel and create a new Macro Enabled Excel Workbook (.xlsm) named Excel_Macros.xlsm
- Save your Spreadsheet in your local drive.
- In the Sheet1 worksheet, go ahead and add the table below:
- Now, from the Ribbon, hit Formulas.
- Then hit Define Name.
- Define a Named Range on which you’ll apply your VBA code as shown below and hit OK.
Use Cell.Font VBA property to change font color and style
- Move to the Developer tab.
- Next go ahead and hit the Visual Basic button.
- In the left hand side Project Explorer, highlight the Excel_Macros.xlsm project then hit Insert and pick Module.
- A new VBA module named Module1 will be created.
- Go ahead and paste the following code in the newly created module:
Sub Color_Cell_Text_Condition() Dim MyCell As Range Dim StatValue As String Dim StatusRange As Range Set StatusRange = Range("Completion_Status") 'loop through all cells in the range For Each MyCell In StatusRange StatValue = MyCell.Value 'modify the cell text values as needed. Select Case StatValue 'green Case "Progressing" With MyCell.Font .Color = RGB(0, 255, 0) .Size = 14 .Bold = True End With 'orange Case "Pending Feedback" With MyCell.Font .Color = RGB(255, 141, 0) .Size = 14 .Bold = True End With 'red Case "Stuck" With MyCell.Font .Color = RGB(255, 0, 0) .Size = 14 .Bold = True End With End Select Next MyCell End Sub
- Hit the Save button in your Visual Basic editor.
- Now hit Run and then pick Run Sub/UserForm (or simply hit F5).
- Move to your Sheet1 worksheet and notice the changes. Your table entries were assigned multiple color codes according to their text (using the RGB color function), and we also set the text to be bold and increase its size.
- If you haven’t saved your code, hit the Save button (or Ctrl+S), then also save your workbook.
Access your VBA Macro
- Note that your code is always available for you to run from the Macros command located in the View tab (or alternatively in Developer | Macros)