To change text color in Excel VBA, set a range’s Font.Color property, usually with RGB(red, green, blue). For example, Range("A1").Font.Color = RGB(255, 0, 0) makes the text in A1 red; use Interior.Color instead when you want to change the cell’s fill.
What VBA text color controls
In Excel, text color is a font-formatting setting. VBA accesses it through the Font object belonging to a cell or range. The color affects the characters in the cell, including displayed numbers and dates, but does not change the cell’s background, border, or number format. Those are separate formatting properties.
A range can be one cell, a block of cells, or a named range. Applying one font color to a multi-cell range changes the font color throughout that range. You do not have to select the cells first, and it is generally better to refer to the range directly than to use Select or Selection. Direct references make a macro less dependent on which worksheet or cell happens to be active.
Keep the distinction between text and fill clear:
Range("A1").Font.Colorcontrols the text color.Range("A1").Interior.Colorcontrols the cell’s background fill.Range("A1").Borders.Colorcontrols border color.
Set a font color with RGB
For a specific color, RGB(red, green, blue) is usually the clearest method. Each component is a number from 0 to 255: the first is red, the second green, and the third blue. For instance, RGB(0, 0, 255) is blue, RGB(0, 128, 0) is green, and RGB(0, 0, 0) is black. The function returns a value VBA can assign to the font’s Color property.
This example colors a heading and a block of cells without selecting either range:
Sub ColorTextWithRGB()
' Set font colors directly on the target ranges.
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1")
ws.Range("A1").Font.Color = RGB(0, 102, 204)
ws.Range("B2:B10").Font.Color = RGB(0, 128, 0)
End Sub
Change Sheet1 and the cell addresses to match your workbook. Using a worksheet variable makes it explicit which sheet the code will affect. If you omit the worksheet reference and write Range("A1") on its own, VBA uses the active sheet, which can lead to an unexpected result if another sheet is selected when the macro runs.
Color only cells that meet a condition
A common task is to highlight text based on cell contents—for example, make negative values red or mark a status such as “Late.” A loop lets VBA inspect each cell and apply formatting only when a test is true. This example assumes statuses are in A2:A20:
Sub ColorLateStatuses()
' Make cells containing Late red.
Dim ws As Worksheet
Dim cell As Range
Set ws = ThisWorkbook.Worksheets("Sheet1")
For Each cell In ws.Range("A2:A20")
If cell.Value = "Late" Then
cell.Font.Color = RGB(192, 0, 0)
End If
Next cell
End Sub
The comparison here is not case-sensitive in the usual VBA setting, so “LATE” also matches. If the cells contain formulas, cell.Value reads their calculated results. For numeric tests, use a condition such as If cell.Value < 0 Then; check that the range contains appropriate values before comparing, especially if it may include error values or text. For large datasets, conditional formatting is often more convenient than a VBA loop because Excel updates the appearance as values change.
RGB, ColorIndex, and theme colors
Font.Color and Font.ColorIndex are related but not interchangeable approaches. Color sets or returns a color value, and RGB is a straightforward way to specify an exact red-green-blue combination. ColorIndex refers to an entry in Excel’s color palette, historically numbered from 1 to 56, rather than describing a color with three components. Palette mappings can differ between workbooks, so a particular index may not look the same everywhere.
For new code that needs a predictable color, RGB is usually easier to read and maintain. ColorIndex can still be useful when working with an older workbook or when a macro specifically needs to use its palette. Excel also supports theme-based font colors. A theme color can follow the workbook’s theme, while a fixed RGB color stays tied to its chosen color value. This matters when a workbook’s theme may be changed to match a company template or presentation style.
For a theme-based color, VBA can use the font’s ThemeColor property, with a value such as xlThemeColorAccent1. A tint or shade can be applied with TintAndShade. Theme-based formatting is useful when you want colors to track the workbook design; RGB is often preferable when exact color reproduction is the priority. Avoid assigning both styles indiscriminately: decide whether the workbook should follow its theme or preserve a fixed color.
Text color versus conditional formatting
There are two ways to make a cell’s text appear in a color. A macro can directly set Font.Color, leaving that formatting in place until another action changes it. Alternatively, a conditional formatting rule can apply a font color when a condition is met. Conditional formatting is usually the better choice for rules that should respond automatically as values change, such as showing overdue dates in red.
To create a rule in Excel for Microsoft 365 on Windows, select the range and choose Home > Conditional Formatting, then select an appropriate rule type. Use the rule’s formatting options to set the font color. On Mac, the Conditional Formatting command is also on the Home tab, though the dialog layout may differ. Excel for the web supports conditional formatting, but it does not run VBA macros; use the desktop app to create or run VBA.
A direct VBA color assignment may appear not to work if a conditional formatting rule controls the displayed font color. The rule can take precedence in what Excel shows, even though the cell also has a manually assigned font color. Check the range’s conditional formatting rules when the displayed result differs from the color set in code. If the color should depend on changing data, edit the rule rather than repeatedly running a macro.
Reset or remove a font color
To return text to Excel’s automatic font color, set Font.ColorIndex to xlColorIndexAutomatic. “Automatic” lets Excel choose the font color according to its formatting context; it is not the same as explicitly assigning black. A reset macro can be useful after applying temporary highlights:
Sub ResetFontColor()
' Return the selected range to automatic font color.
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1")
ws.Range("A2:A20").Font.ColorIndex = xlColorIndexAutomatic
End Sub
This changes the underlying font color for the specified cells. It does not remove conditional formatting rules, so a conditional rule may continue to display a color. If your intention is to remove the rule as well, manage the conditional formatting separately rather than expecting a font-color reset to erase it.
Run and save a VBA macro safely
On Windows, open the Visual Basic Editor with Alt+F11. Choose Insert > Module, paste a procedure into the module, and run it from the editor with F5 while the procedure is active. You can also run a public macro through Excel’s Macro dialog, commonly opened with Alt+F8. On Mac, Option+F11 opens the Visual Basic Editor on many keyboards; the Fn key may be needed depending on keyboard settings. The Developer tab’s Visual Basic command is another route where available.
Save a workbook containing VBA in a macro-enabled format such as .xlsm; saving as a standard .xlsx file does not preserve VBA code. Enable macros only for workbooks you trust, and follow your organization’s security policies. Before running a macro that changes formatting, save a copy of the workbook. Macro actions cannot be undone with Ctrl+Z on Windows or Command+Z on Mac, so a backup is the safest way to recover if a macro affects the wrong range.
Common problems and how to fix them
- The fill changes instead of the text. Check that the code uses
Font.Color, notInterior.Color. - The wrong worksheet is affected. Qualify the range with a worksheet, such as
ws.Range("A1"), and setwsto the intended sheet. - The color does not show. Look for conditional formatting, a font effect, or another macro that may be setting the appearance. Confirm that the code ran and that the address is correct.
- The color differs from the expected shade. Check the RGB order—red, green, then blue—and keep each value between 0 and 255. For example,
RGB(255, 0, 0)is red, not blue. - The macro is missing after saving. Save as
.xlsmand confirm that macros are permitted in the file’s security settings. - Nothing runs in Excel for the web. VBA execution requires desktop Excel. Open the workbook in Excel for Windows or Mac to run the macro.
Frequently Asked Questions
How do I change text color in Excel VBA?
Assign a color to the range’s font, for example Range("A1").Font.Color = RGB(255, 0, 0). Qualify the range with a worksheet when you need to ensure the macro targets a specific sheet.
How do I change the color of a cell’s text, not its background?
Use Font.Color for text. Use Interior.Color for the cell fill. These are separate properties, so changing one does not normally change the other.
How do I make negative numbers red with VBA?
Loop through the cells and set cell.Font.Color when a numeric value is below zero. If the rule should update automatically whenever values change, create a conditional formatting rule instead.
How do I restore the default font color?
Set the range’s Font.ColorIndex to xlColorIndexAutomatic. This restores automatic font color, but it does not remove conditional formatting that may also affect how the text appears.
Can Excel for the web run a VBA macro?
No. Excel for the web can open and edit many macro-enabled workbooks, but it cannot run VBA. Use the Excel desktop application on Windows or Mac to execute the code.
Related
- Excel Shortcut to Insert a Cut Row
- Excel Shortcut to Insert Another Row in Windows and Mac
- Excel Shortcut to Select to the End of a Column
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA: How to Stop an Infinite Loop Safely in ExcelTo stop a running Excel VBA macro, press Ctrl+Break on Windows, then choose End to stop it or…
- VBA Double Quotes in Strings: How to Escape a QuoteTo include a double quote inside a VBA string, type two double quotes in a row: VBA treats…
- VBA Code for UserForm Data Entry: Build a Reliable FormTo enter records through an Excel VBA UserForm, create text boxes and a combo box on the form,…
- Excel VBA Set Column Width: Code, Units, and Common FixesTo set a column width in Excel VBA, assign a value to the column’s ColumnWidth property, such as…
- VBA Error Handling Examples for Reliable MacrosVBA error handling examples usually use On Error GoTo to catch unexpected failures, On Error Resume Next briefly…
- VBA Array Set: Assigning and Initializing Arrays in ExcelIn VBA, you do not use Set to assign an array: use ordinary assignment for array values, and…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.