🚀The world's best VBA AI has evolved. ExcelMaster is now an autonomous Agent.Read more →
Back to Blog

VBA Font in Excel — Set Color, Bold and Size in Code (and the ColorIndex Trap)

|

VBA Font in Excel — Set Color, Bold and Size in Code (and the ColorIndex Trap)

TL;DR — Range.Font is the text layer of a cell: color, bold, italic, size, name. None of it changes the value underneath — a red number is still just a number. Color is the one part that trips people up, because there are three ways to set it and two of them live in different number spaces. Use Range("A1").Font.Color = RGB(200, 0, 0) for any color you want. Use Range("A1").Font.ColorIndex = 3 only if you are working with Excel's old 56-slot palette — and never mix the two, because .Color = 3 and .ColorIndex = 3 are completely different reds.

' Make the header row bold and dark blue - one statement per property.
Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Sales")
With ws.Range("A1:F1").Font
    .Bold = True
    .Color = RGB(0, 32, 96)     ' 24-bit RGB - what you want 95% of the time
    .Size = 12
End With

Changing how text looks is the first thing most people automate in VBA, and it is also where the most confusing bug in the whole language hides — the difference between .Color and .ColorIndex. This guide is built around one idea that makes the rest obvious: the Font object controls appearance only, and its color property comes in two incompatible flavours. Get that straight and bold, size, name and partial formatting all fall into place.

What you'll learn

  • The mental model — Font is the text layer, and it never touches the value
  • The rule that matters most — .Color (RGB) and .ColorIndex (palette) are different number spaces
  • Bold, italic, size and name — the rest of the text layer, set with With
  • Formatting part of a cell with Characters, and when it silently does nothing
  • Why value-based coloring belongs to Conditional Formatting, not a loop
  • Reading a font color back without getting a surprise number

The mental model: Font is the text layer

A cell has three visual layers that VBA reaches through three different objects: the text (its Font), the fill behind it (its Interior), and how the number is displayed (its NumberFormat). This guide is the first layer. Every property under Range.Font — Color, Bold, Italic, Underline, Size, Name, Strikethrough — changes how the characters look and nothing else.

That is the whole point, and it is worth saying plainly because it is the root of a common misunderstanding: coloring text does not tag it. A cell whose font you turn red still holds the same number, still feeds the same formulas, and is invisible to SUM, SUMIF and IF. Color is for a human's eyes, not for Excel's calculation engine. If you ever catch yourself using font color to mean something — "red cells are overdue" — put that meaning in a real value somewhere a formula can read, and let the color be a consequence.

The rule that matters most: Color and ColorIndex are different number spaces

Here is the one thing to take away. There are two color properties on Font, and they do not speak the same language.

' .Color takes a 24-bit RGB value. Build it with RGB(red, green, blue), 0-255 each.
ws.Range("A1").Font.Color = RGB(200, 0, 0)      ' a strong red

' .ColorIndex takes a palette slot, 1 to 56, from Excel's legacy color table.
ws.Range("A2").Font.ColorIndex = 3              ' also red - but a DIFFERENT red

Both lines turn text red, so they look interchangeable. They are not. .Color = 3 would not give you ColorIndex 3's red — it would give you RGB(3, 0, 0), a near-black. The number 3 means "palette slot 3" to ColorIndex and "the tiny RGB value 3" to Color. Feeding one property the other's number is the number-one font bug, and it fails quietly: no error, just the wrong color.

The rule is simple. Set color with .Color = RGB(r, g, b) — it covers all 16 million colors and reads exactly the way you wrote it. Reach for .ColorIndex only in two situations: you are matching an existing palette-based workbook, or you want one of the two special values that have no RGB equivalent:

ws.Range("A1").Font.ColorIndex = xlColorIndexAutomatic   ' back to the theme's automatic color
ws.Range("A1").Font.ColorIndex = xlColorIndexNone        ' (Interior only, shown later)

xlColorIndexAutomatic is genuinely useful — it resets font color to "automatic" (usually black, but theme-aware), which no single RGB value expresses. For everything else, .Color with RGB is the honest choice.

Bold, size, name: the rest of the text layer

The other font properties are refreshingly boring — they are plain booleans, numbers and strings. Because you usually set several at once, With keeps it readable and avoids re-resolving the range each time (the same reason covered in VBA With):

With ws.Range("B2:B50").Font
    .Bold = True
    .Italic = False
    .Underline = xlUnderlineStyleSingle
    .Size = 11                 ' points
    .Name = "Calibri"
    .Color = RGB(0, 0, 0)
End With

A couple of things worth knowing. .Bold, .Italic and .Strikethrough are True/False. .Size is in points, not pixels. .Name is the typeface as a string, and if you misspell it or name a font that is not installed, Excel silently substitutes a default rather than raising an error — so a "nothing happened" bug is usually a typo in the font name.

Formatting part of a cell: Characters()

Everything above formats the whole cell. To style part of the text — bold the first word, color a suffix — use Characters(start, length):

' Bold only the first 4 characters of A1.
ws.Range("A1").Characters(Start:=1, Length:=4).Font.Bold = True

This is powerful, but it has one hard limit that catches people: Characters only works on a cell that holds a literal string. If the cell contains a formula, or a number, or a date, there are no editable characters to address and the call does nothing (or errors). Partial formatting is a property of typed-in text, not of computed results — so it is for labels and notes, not for cells your code will overwrite.

Don't loop to color by value — that is Conditional Formatting's job

This is the opinionated part, and it saves the most grief. The classic beginner macro walks a range and colors cells that meet a condition:

' Tempting, but it goes stale the moment the data changes.
Dim c As Range
For Each c In ws.Range("B2:B1000")
    If c.Value < 0 Then c.Font.Color = RGB(200, 0, 0)
Next c

It works exactly once. The color is a snapshot taken when the loop ran; edit a value afterward and the formatting is now wrong, because nothing re-evaluates it. Value-driven color is precisely what Conditional Formatting exists for, and you can set that up from VBA too:

' Set the RULE once; Excel keeps it correct forever.
With ws.Range("B2:B1000").FormatConditions
    .Delete
    .Add(Type:=xlCellValue, Operator:=xlLess, Formula1:="0").Font.Color = RGB(200, 0, 0)
End With

Use a direct Font.Color loop only for static, one-time marking — a report you are about to export to PDF, a snapshot you want frozen. The moment the coloring should track live data, add a FormatCondition instead. Reaching for a loop where a rule belonged is the most common reason "my highlighting is wrong" tickets exist.

Reading a font color back

Setting color is easy; reading it back has a trap. If you set with .Color, read with .Color; if you set with .ColorIndex, read with .ColorIndex. Reading the property you did not set gives you a translated, often surprising number:

ws.Range("A1").Font.Color = RGB(200, 0, 0)
Debug.Print ws.Range("A1").Font.Color        ' 13312  (the 24-bit number, = RGB 200,0,0)
Debug.Print ws.Range("A1").Font.ColorIndex   ' 3 or a nearby palette match - Excel approximates

.Color returns the exact 24-bit value (note it is stored as BGR internally, so decoding it by hand is fiddly — compare against RGB(...) rather than eyeballing the integer). .ColorIndex on a cell colored with .Color returns Excel's nearest palette guess, which may not round-trip. The rule mirrors the setting rule: stay in one number space. If your code both sets and checks font colors, pick .Color and use it on both sides.

How ExcelMaster helps

Font formatting looks trivial until the details pile up: RGB versus ColorIndex, a font name that silently falls back, Characters that does nothing on a formula cell, and a coloring loop that was supposed to be a Conditional Formatting rule. Each one fails quietly — the wrong color, no change, or highlighting that drifts out of date.

ExcelMaster lets you describe the result instead. Say "bold the header row and color negatives red" or "highlight rows where status is Overdue," and it writes Font.Color = RGB(...) for a static pass or a FormatCondition when the color should track the data — and sets the whole range at once instead of looping. You keep the workbook and the code; you skip the part where a 3 in the wrong property paints everything the wrong shade.

Frequently asked questions

How do I change font color in Excel VBA?

Use Range("A1").Font.Color = RGB(red, green, blue), with each value from 0 to 255 — for example RGB(200, 0, 0) for red. RGB covers every color, sets exactly what you specify, and reads back the same way. Only use Range("A1").Font.ColorIndex = n (a 1 to 56 palette slot) when you are matching an old palette-based workbook, and never assume the two numbers mean the same color.

What is the difference between Font.Color and Font.ColorIndex?

.Color takes a 24-bit RGB number built with RGB(r, g, b), giving access to all 16 million colors. .ColorIndex takes an index into Excel's legacy 56-color palette. The same integer means different things to each — .Color = 3 is near-black RGB(3,0,0), while .ColorIndex = 3 is red. Prefer .Color for setting, and read back with the same property you set with.

How do I make a cell bold in VBA?

Range("A1").Font.Bold = True, and False to remove it. Bold, Italic and Strikethrough are all True/False properties of the Font object. To set several font properties at once, wrap them in a With Range(...).Font ... End With block so you name the range only once.

How do I color only part of the text in a cell?

Use Range("A1").Characters(Start:=1, Length:=4).Font.Bold = True to style a substring. This only works when the cell contains typed-in text — on a formula, number or date there are no editable characters and the call does nothing. Use it for labels and notes, not for cells your code will recompute.

Why does my VBA coloring stop being correct when I edit the sheet?

Because a Font.Color loop paints a one-time snapshot; it does not re-run when the data changes. For color that should track values — negatives in red, overdue rows highlighted — add a Conditional Formatting rule with Range(...).FormatConditions.Add, which Excel re-evaluates automatically. Use a direct coloring loop only for a static export you want frozen.

Tested in

Tested in: Excel 365 (Windows 11), VBA 7.1 — last verified 2026-08-11.

Related guides: VBA Cell Color · VBA NumberFormat · VBA With · VBA Range · VBA For Loop