How to change font color in Excel based on value

In this Excel VBA Change Font Color Based on Cell Value Tutorial, you learn how to change a cell's font color based on a cell's value with Excel macros.

Table of Contents Show

The VBA Change Font Color Based on Cell Value Snippet Template/Structure
If… Then… Else Statement Version of the VBA Change Font Color Based on Cell Value Snippet Template/Structure
Select Case Statement Version of the VBA Change Font Color Based on Cell Value Snippet Template/Structure
The Example Before VBA Change Font Color Based on Cell Value
Step 1 Example
Step 2: Test the Cell's Value, and Instruct Excel to Execute the Applicable Statement (Changing a Cell's Font Color) Depending on the Cell's Value
If… Then… Else Statement Version
Select Case Statement Version
Step 2 Example
Step 3: Refer to the Cell Whose Font Color You Change (Based on a Cell's Value)
Step 3 Example
Step 4: Set the Value of the Font.Color or Font.ColorIndex Property
If… Then… Else Statement Version
Select Case Statement Version
Step 4 Example
Download the VBA Change Font Color Based on Cell Value Example Workbook

You can (also) change a cell's font color based on a cell's value with Excel's Conditional Formatting. In some cases, Conditional Formatting may be the more appropriate tool (instead of VBA macros) to change a cell's font color based on a cell's value.

This Excel VBA Change Font Color Based on Cell Value Tutorial is accompanied by an Excel workbook with the data and VBA code I use when describing the step-by-step process below. Get this example workbook (for free) by clicking the button below.

The VBA code in the Excel workbook that accompanies this Excel VBA Change Font Color Based on Cell Value Tutorial is (always) stored in the Visual Basic Editor (VBE). If you don't know how to work with the VBE, I suggest you read my Visual Basic Editor (VBE) Tutorial. I link to this Tutorial in the Related Excel Macro and VBA Training Materials and Resources Section below.

The following Excel Macro and VBA Tutorials may help you better understand and implement the contents below.

This Excel VBA Change Font Color Based on Cell Value Tutorial is part of a more comprehensive series of Excel VBA Font Color Tutorials.

You can find more Excel and VBA Tutorials in the organized Tutorials Archive: Click here to visit the Archives.

If you want to learn how to automate Excel (and save time) by working with macros and VBA, you may be interested in the following Premium Excel Macro and VBA Training Materials:

If you want to save time when working with macros and VBA, you may be interested in AutoMacro: Click here to learn more about AutoMacro (affiliate link). AutoMacro is an add-in for VBA that installs directly into the VBE. Depending on the version, AutoMacro comes loaded with:

Code generators.
An extensive code library.
The ability to create your own code library.
Advanced coding tools.

If you need consulting services, you may want to consider working with ExcelRescue. ExcelRescue is my usual suggestion for people who (like you) may need help with Excel tasks/projects: Click here to visit ExcelRescue (affiliate link).

The VBA Change Font Color Based on Cell Value Snippet Template/Structure

The following are the 2 versions of the VBA change font color based on cell value snippet template/structure I explain (step-by-step) in the Sections below.

If… Then… Else Statement Version of the VBA Change Font Color Based on Cell Value Snippet Template/Structure

'Source: https://powerspreadsheets.com/ 'More information: https://powerspreadsheets.com/vba-font-color-value/ If WorkbookObjectReferenceForValue.WorksheetObjectReferenceForValue.CellRangeObjectReferenceForValue.Value ComparisonOperator CriterionValue Then CellRangeObjectReferenceForFontColor.Font.ColorOrColorIndex = NewColorOrColorIndexValueIfTrue End If

Select Case Statement Version of the VBA Change Font Color Based on Cell Value Snippet Template/Structure

'Source: https://powerspreadsheets.com/ 'More information: https://powerspreadsheets.com/vba-font-color-value/ Select Case WorkbookObjectReferenceForValue.WorksheetObjectReferenceForValue.CellRangeObjectReferenceForValue.Value Case CriterionValue1: CellRangeObjectReferenceForFontColor.Font.ColorOrColorIndex = NewColorOrColorIndexValue1 Case CriterionValue2: CellRangeObjectReferenceForFontColor.Font.ColorOrColorIndex = NewColorOrColorIndexValue2 '... Case CriterionValue#: CellRangeObjectReferenceForFontColor.Font.ColorOrColorIndex = NewColorOrColorIndexValue# Case Else: CellRangeObjectReferenceForFontColor.Font.ColorOrColorIndex = NewColorOrColorIndexValueElse End Select

The Example Before VBA Change Font Color Based on Cell Value

The example worksheet has 1 table (cells A6 to B26) with the following characteristics:

2 columns (Value, Change Font Color Based on Cell Value Result).
1 header row (row 6).
20 entries (rows 7 to 26).

Refer to the cell whose value you consider when changing a cell's font color.

In other words: Create a VBA expression that returns a Range object representing the cell whose value determines the font color.

Consider explicitly including the following references to create a fully qualified object reference returning a Range object:

A reference to the applicable workbook. The following VBA constructs (among others) may return a Workbook object:
- The Application.ThisWorkbook property.
- The Application.Workbooks and Workbooks.Item properties.
- The Application.ActiveWorkbook property.
A reference to the applicable worksheet. The following VBA constructs (among others) may return a Worksheet object:
- The Workbook.Sheets and Sheets.Item properties.
- The Workbook.Worksheets and Worksheets.Item properties.
- The Application.ActiveSheet property.
A reference to the applicable cell range. The following VBA constructs (among others) may return a Range object:
- The Worksheet.Range property.
- The Worksheet.Cells and Range.Item properties.
- The Worksheet.Rows property.
- The Worksheet.Columns property.
- The Range.Range property.
- The Range.Cells and Range.Item properties.
- The Range.Rows property.
- The Range.Columns property.
- The Range.EntireRow property.
- The Range.EntireColumn property.
- The Range.Offset property.
- The Range.CurrentRegion property.
- The Range.End property.
- The Range.Resize property.

'Source: https://powerspreadsheets.com/ 'More information: https://powerspreadsheets.com/vba-font-color-value/ WorkbookObjectReferenceForValue.WorksheetObjectReferenceForValue.CellRangeObjectReferenceForValue

Step 1 Example

I do the following:

(1) Declare an object variable:

As of the Range object data type.
With the name “iCell”.
To act as the iteration object variable for the For Each… Next loop I create below.

'Source: https://powerspreadsheets.com/ 'More information: https://powerspreadsheets.com/vba-font-color-value/ Dim iCell As Range

(2) Create a For Each… Next loop with the following characteristics:

The iCell object variable is the loop's iteration object variable.
The loop iterates through all cells between cell A7 and cell A26 in the “VBA Font Color Based on Value” worksheet in the workbook where the procedure is stored.

I work with the following constructs to obtain the cell range the For Each… Next loop works with:

The Application.ThisWorkbook property: ThisWorkbook.
The Workbook.Worksheets and Worksheets.Item properties: Worksheets(“VBA Font Color Based on Value”).
The Worksheet.Range property: Range(“A7:A26”).

The iCell object variable represents the cell the loop is currently iterating through.

Step 2: Test the Cell's Value, and Instruct Excel to Execute the Applicable Statement (Changing a Cell's Font Color) Depending on the Cell's Value

Work with one of the following statements to conditionally execute a statement (changing a cell's font color):

If… Then… Else.
Select Case.

If… Then… Else Statement Version

If you choose to work with the If… Then… Else statement to conditionally execute the statement changing a cell's font color:

The conditional test(s) (on which statement execution depends) test(s) whether the value stored in the cell whose value you consider when changing a cell's font color (which you refer to in step #1) meets the applicable condition (for changing font color).
The statement to execute (depending on the value returned by the applicable conditional test(s)) is that changing the applicable cell's font color. You build this statement in step #4.

Depending on the case you deal with, you may need to work with different versions of the If… Then… Else statement. Consider the following 4 basic versions of the If… Then… Else statement:

If… Then. This is the version I use in this Excel VBA Change Font Color Based on Cell Value Tutorial.
If… Then… Else.
If… Then… ElseIf.
If… Then… ElseIf… Else.

'Source: https://powerspreadsheets.com/ 'More information: https://powerspreadsheets.com/vba-font-color-value/ If ConditionalTest Then StatementFromStep4 End If

Use the following 3 elements to create the conditional test(s) inside the If… Then… Else statement:

The value stored in the cell (whose value you consider when changing a cell's font color).
- You created the applicable Range object reference in step #1.
- As a general rule: Work with the Range.Value property to get the cell's value.
A comparison operator.
- Comparison operators:
  - Carry out comparisons.
  - (Usually) Return a Boolean value (True or False).
- The following are commonly used comparison operators:
  - Less than (<).
  - Less than or equal to (<=).
  - Greater than (>).
  - Greater than or equal to (>=).
  - Equal to (=).
  - Not equal to (<>).
An expression with the criterion (value) you use to determine whether the condition (for changing a cell's font color) is met.

Considering both:

The If… Then… Else statement; and
The conditional test;

The basic template/structure of the full If… Then block is as follows:

Select Case Statement Version

Start with the basic Select Case statement structure/template.

'Source: https://powerspreadsheets.com/ 'More information: https://powerspreadsheets.com/vba-font-color-value/ Select Case TestExpression Case CaseExpression1: CaseStatement1 Case CaseExpression2: CaseStatement2 '... Case CaseExpression#: CaseStatement# Case Else: ElseStatement End Select

If you choose to work with the Select Case statement to conditionally execute the statement changing a cell's font color:

The test expression used to identify the statement (changing a cell's font color) to execute is the value stored in the cell (whose value you consider when changing a cell's font color).
- You created the applicable Range object reference in step #1.
- As a general rule: Work with the Range.Value property to get the cell's value.
The case expressions used to identify the statement (changing a cell's font color) to execute are the criteria (values) you use to determine the statement (changing a cell's font color) to execute.
The statement to execute (for each case expression) is that changing the applicable cell's font color. You build this statement in step #4.

Therefore, the basic template/structure of the Select Case block is as follows:

Step 2 Example

The statements inside the Select Case block in the VBA font color based on value example macro start with a reference to the cell the loop is currently iterating through (represented by the iCell object variable I declared in step #1).

Therefore, I work with a With… End With block. The statements inside the With… End With block work with the cell represented by the iCell object variable.

'Source: https://powerspreadsheets.com/ 'More information: https://powerspreadsheets.com/vba-font-color-value/ With iCell Select Case TestExpression Case CaseExpression1: CaseStatement1 Case CaseExpression2: CaseStatement2 '... Case CaseExpression#: CaseStatement# Case Else: ElseStatement End Select End With

The test expression inside the Select Case statement is the value stored in the cell represented by the iCell object variable. I use the Range.Value property to get this cell's value.

'Source: https://powerspreadsheets.com/ 'More information: https://powerspreadsheets.com/vba-font-color-value/ With iCell Select Case .Value Case CaseExpression1: CaseStatement1 Case CaseExpression2: CaseStatement2 '... Case CaseExpression#: CaseStatement# Case Else: ElseStatement End Select End With

The case expressions inside the Select Case statement are the following values:

'Source: https://powerspreadsheets.com/ 'More information: https://powerspreadsheets.com/vba-font-color-value/ With iCell Select Case .Value Case 1: CaseStatement1 Case 2: CaseStatement2 Case 3: CaseStatement3 Case 4: CaseStatement4 Case 5: CaseStatement5 Case 6: CaseStatement6 Case 7: CaseStatement7 Case 8: CaseStatement8 End Select End With

The statement to execute (for each case expression inside the Select Case statement) is that changing the applicable cell's font color. I build this statement in step #4.

'Source: https://powerspreadsheets.com/ 'More information: https://powerspreadsheets.com/vba-font-color-value/ With iCell Select Case .Value Case 1: StatementFromStep4ForValue1 Case 2: StatementFromStep4ForValue2 Case 3: StatementFromStep4ForValue3 Case 4: StatementFromStep4ForValue4 Case 5: StatementFromStep4ForValue5 Case 6: StatementFromStep4ForValue6 Case 7: StatementFromStep4ForValue7 Case 8: StatementFromStep4ForValue8 End Select End With

Step 3: Refer to the Cell Whose Font Color You Change (Based on a Cell's Value)

Refer to the cell whose font color you change (based on a cell's value).

In other words: Create a VBA expression that returns a Range object representing the cell whose font color you change (based on a cell's value).

The cell whose font color you change (based on a cell's value) can be either of the following:

The same cell whose value you consider (when changing the cell's font color). You created the applicable Range object reference in step #1.
A different cell from that whose value you consider (when changing a cell's font color).

Consider whether explicitly including the following references to create a fully qualified object reference returning a Range object is necessary (or advisable):

A reference to the applicable workbook.
A reference to the applicable worksheet.
A reference to the applicable cell range.

In step #1, I list several VBA constructs that may return a Workbook object, a Worksheet object, or a Range object.

'Source: https://powerspreadsheets.com/ 'More information: https://powerspreadsheets.com/vba-font-color-value/ CellRangeObjectReferenceForFontColor

Step 3 Example

I work with the Range.Offset property to refer to the cell one column to the right of the cell represented by the iCell object variable (I declared in step #1). I set the parameters of the Range.Offset property as follows:

RowOffset: 0.
ColumnOffset: 1.

'Source: https://powerspreadsheets.com/ 'More information: https://powerspreadsheets.com/vba-font-color-value/ iCell.Offset(0, 1)

Step 4: Set the Value of the Font.Color or Font.ColorIndex Property

Use either of the following 2 properties to set the applicable cell's font color:

Font.Color.
Font.ColorIndex.

Do the following to set the value of the Font.Color or Font.ColorIndex property:

(1) Start with the Range object reference you created in step #3.

'Source: https://powerspreadsheets.com/ 'More information: https://powerspreadsheets.com/vba-font-color-value/ CellRangeObjectReferenceForFontColor

(2) Refer to the following:

The Range.Font property; and
The Font.Color or Font.ColorIndex property (as applicable).

'Source: https://powerspreadsheets.com/ 'More information: https://powerspreadsheets.com/vba-font-color-value/ CellRangeObjectReferenceForFontColor.Font.ColorOrColorIndex

(3) Set the value of the Font.Color property or Font.ColorIndex property.

If you don't know how to work with the Font.Color or Font.ColorIndex properties, I suggest you read the applicable Tutorials I link to in the Related Excel Macro and VBA Training Materials and Resources Section above.

'Source: https://powerspreadsheets.com/ 'More information: https://powerspreadsheets.com/vba-font-color-value/ CellRangeObjectReferenceForFontColor.Font.ColorOrColorIndex = NewColorOrColorIndexValue

(4) Add the statement(s) setting the value of the Font.Color or Font.ColorIndex property to (as applicable):

The If… Then… Else statement; or
The Select Case statement.

You created the applicable (If… Then… Else or Select Case) statement in step #2.

Depending on the case you deal with, you may have to add several statements setting the value of the Font.Color or Font.ColorIndex property. The following are situations where this may happen:

If you work with one of the following versions of the If… Then… Else statement:
- If… Then… Else.
- If… Then… ElseIf.
- If… Then… ElseIf… Else.
If your Select Case statement has several case expressions.

As a general rule: The basic template/structure (of the statement(s) setting the value of the Font.Color or Font.ColorIndex property) you learn in this Section is applicable to those (several) statements.

If… Then… Else Statement Version

(1) Start with the If… Then… Else statement you created in step #2.

(2) When considering the statement (setting the value of the Font.Color or Font.ColorIndex property) you create in this step #4:

Select Case Statement Version

(1) Start with the Select Case statement you created in step #2.

(2) When considering the statements (setting the value of the Font.Color or Font.ColorIndex property) you create in this step #4:

Step 4 Example

(1) I start with the Select Case statement I created in step #2.

(2) The Select Case statement requires 8 different versions of the statement setting the value of the Font.Color or Font.ColorIndex property.

I set the value of the Font.Color property to the following VBA color constants:

vbBlack.
vbRed.
vbGreen.
vbYellow.
vbBlue.
vbMagenta.
vbCyan.
vbWhite.

Considering the Range object reference I created in step #3:

'Source: https://powerspreadsheets.com/ 'More information: https://powerspreadsheets.com/vba-font-color-value/ iCell.Offset(0, 1).Font.Color = vbBlack iCell.Offset(0, 1).Font.Color = vbRed iCell.Offset(0, 1).Font.Color = vbGreen iCell.Offset(0, 1).Font.Color = vbYellow iCell.Offset(0, 1).Font.Color = vbBlue iCell.Offset(0, 1).Font.Color = vbMagenta iCell.Offset(0, 1).Font.Color = vbCyan iCell.Offset(0, 1).Font.Color = vbWhite

(3) I add these statements (setting the value of the Font.Color property) to the Select Case block.

Considering the With… End With block I created in step #2, I remove the explicit references to the iCell object variable in the individual statements inside the Select Case block.

'Source: https://powerspreadsheets.com/ 'More information: https://powerspreadsheets.com/vba-font-color-value/ With iCell Select Case .Value Case 1: .Offset(0, 1).Font.Color = vbBlack Case 2: .Offset(0, 1).Font.Color = vbRed Case 3: .Offset(0, 1).Font.Color = vbGreen Case 4: .Offset(0, 1).Font.Color = vbYellow Case 5: .Offset(0, 1).Font.Color = vbBlue Case 6: .Offset(0, 1).Font.Color = vbMagenta Case 7: .Offset(0, 1).Font.Color = vbCyan Case 8: .Offset(0, 1).Font.Color = vbWhite End Select End With

(4) I nest this With… End With block inside the For Each… Next loop I created in step #1.

'Source: https://powerspreadsheets.com/ 'More information: https://powerspreadsheets.com/vba-font-color-value/ Dim iCell As Range For Each iCell In ThisWorkbook.Worksheets("VBA Font Color Based on Value").Range("A7:A26") With iCell Select Case .Value Case 1: .Offset(0, 1).Font.Color = vbBlack Case 2: .Offset(0, 1).Font.Color = vbRed Case 3: .Offset(0, 1).Font.Color = vbGreen Case 4: .Offset(0, 1).Font.Color = vbYellow Case 5: .Offset(0, 1).Font.Color = vbBlue Case 6: .Offset(0, 1).Font.Color = vbMagenta Case 7: .Offset(0, 1).Font.Color = vbCyan Case 8: .Offset(0, 1).Font.Color = vbWhite End Select End With Next iCell

The full VBA font color based on value example macro is as follows:

Sub ChangeFontColorBasedOnCellValue() 'Source: https://powerspreadsheets.com/ 'More information: https://powerspreadsheets.com/vba-font-color-value/ 'Step 1: Declare iteration object variable Dim iCell As Range 'Step 1: Loop through all cells between cells A7 and A26 in the "VBA Font Color Based on Value" worksheet in this workbook For Each iCell In ThisWorkbook.Worksheets("VBA Font Color Based on Value").Range("A7:A26") 'Step 1: Refer to the cell the loop is currently iterating through With iCell 'Step 2: Use the value stored in the cell the loop is currently iterating through to identify the statement to execute Select Case .Value 'Do the following: 'Step 2: Execute the applicable statement (based on the applicable cell value) 'Step 3: Refer to the cell one column to the right of the cell the loop is currently iterating through 'Step 4: Set the value of the Font.Color property to a VBA color constant Case 1: .Offset(0, 1).Font.Color = vbBlack Case 2: .Offset(0, 1).Font.Color = vbRed Case 3: .Offset(0, 1).Font.Color = vbGreen Case 4: .Offset(0, 1).Font.Color = vbYellow Case 5: .Offset(0, 1).Font.Color = vbBlue Case 6: .Offset(0, 1).Font.Color = vbMagenta Case 7: .Offset(0, 1).Font.Color = vbCyan Case 8: .Offset(0, 1).Font.Color = vbWhite End Select End With Next iCell End Sub

The following GIF illustrates the effects of using the VBA font color based on value example macro.

Download the VBA Change Font Color Based on Cell Value Example Workbook

This Excel VBA Change Font Color Based on Cell Value Tutorial is accompanied by an Excel workbook with the data and VBA code I use when describing the step-by-step process above. Get this example workbook (for free) by clicking the button below.

The following Excel Macro and VBA Tutorials may help you better understand and implement the contents above.

This Excel VBA Change Font Color Based on Cell Value Tutorial is part of a more comprehensive series of Excel VBA Font Color Tutorials.

You can find more Excel and VBA Tutorials in the organized Tutorials Archive: Click here to visit the Archives.

If you want to learn how to automate Excel (and save time) by working with macros and VBA, you may be interested in the following Premium Excel Macro and VBA Training Materials: