How to set Excel text font color with VBA?

by

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.

Preliminaries

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)