• Login
  • Courses
  • Books
  • Cheat Sheets
  • Tutorials Archive
  • VBA Code Generator
  • Contact

Power Spreadsheets

Excel and VBA tutorials and training. Learn how to use Microsoft Excel and Visual Basic for Applications now.

Excel VBA Change Font Color Based on Cell Value in 4 Easy Steps (+ Free Easy-To-Adjust Excel Workbook Example)

Excel VBA Change Font Color Based on Cell Value in 4 Easy Steps (+ Free Easy-To-Adjust Excel Workbook Example)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.

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.


Get immediate free access to the Excel VBA Change Font Color Based on Cell Value workbook example

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.

Table of Contents

  • Related Excel Macro and VBA Training Materials and Resources
  • 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: Refer to the Cell Whose Value You Consider
    • 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
  • Related Excel Macro and VBA Training Materials and Resources

Related Excel Macro and VBA Training Materials and Resources

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

  • Tutorials about general macro and VBA constructs and structures:
    • Tutorials for Beginners:
      • Excel Macros: Click here to open.
      • Excel VBA: Click here to open.
    • Enable macros in Excel: Click here to open.
    • Work with the Visual Basic Editor (VBE): Click here to open.
    • Create Sub procedures: Click here to open.
    • Refer to objects (click here to open), including:
      • Sheets: Click here to open.
      • Cell ranges: Click here to open.
    • Work with:
      • Properties: Click here to open.
      • Variables: Click here to open.
      • Data types: Click here to open.
      • Functions: Click here to open.
      • Events: Click here to open.
      • R1C1-style references: Click here to open.
      • The Select Case statement: Click here to open.
      • Loops: Click here to open.
  • Tutorials with practical VBA applications and macro examples:
    • Activate a workbook: Click here to open.
    • Find the last row and last column: Click here to open.
    • Work with the Value property: Click here to open.
    • Check if a cell is empty: Click here to open.
    • Work with font characteristics: Click here to open.
    • Work with Excel's AutoFilter: Click here to open.
    • Search and find: Click here to open.

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

  • Excel VBA Font Color Index in 2 Easy Steps: Click here to open.
  • Excel VBA Font Color RGB in 2 Easy Steps: Click here to open.
  • Excel VBA Font Color HEX in 5 Easy Steps: Click here to open.
  • Excel VBA Change Font Color for Part of Text in 8 Easy Steps: Click here to open.
  • Excel VBA Text Box Font Color in 2 Easy Steps: Click here to open.
  • Excel VBA ActiveX Text Box Font Color in 2 Easy Steps: Click here to open.
  • Excel VBA UserForm Text Box Font Color in 2 Easy Steps: Click here to open.
  • Excel VBA Chart Data Label Font Color in 4 Easy Steps: Click here to open.
  • Excel VBA ActiveX Label Font Color in 2 Easy Steps: Click here to open.
  • Excel VBA UserForm Label Font Color in 2 Easy Steps: Click here to open.

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:

  • Premium Courses at the Power Spreadsheets Academy: Click here to open.
  • Books at the Power Spreadsheets Library: Click here to open.
  • VBA Cheat Sheets: Click here to open.

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).


Get immediate free access to the Excel VBA Change Font Color Based on Cell Value workbook example

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


Get immediate free access to the Excel VBA Change Font Color Based on Cell Value workbook example

The Example Before VBA Change Font Color Based on Cell 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.


Get immediate free access to the Excel VBA Change Font Color Based on Cell Value workbook example

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 above.

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).
Example data for Excel VBA change font color based on cell value


Get immediate free access to the Excel VBA Change Font Color Based on Cell Value workbook example

Step 1: Refer to the Cell Whose Value You Consider

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:

  1. 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.
  2. 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.
  3. 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


Get immediate free access to the Excel VBA Change Font Color Based on Cell Value workbook example

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”).
'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")
    '...
Next iCell

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


Get immediate free access to the Excel VBA Change Font Color Based on Cell Value workbook 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

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:

  1. 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.
  2. 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 (<>).
  3. An expression with the criterion (value) you use to determine whether the condition (for changing a cell's font color) is met.
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vba-font-color-value/
WorkbookObjectReferenceForValue.WorksheetObjectReferenceForValue.CellRangeObjectReferenceForValue.Value ComparisonOperator CriterionValue

Considering both:

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

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

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

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:

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vba-font-color-value/
Select Case WorkbookObjectReferenceForValue.WorksheetObjectReferenceForValue.CellRangeObjectReferenceForValue.Value
    Case CriterionValue1: StatementFromStep4ForValue1
    Case CriterionValue2: StatementFromStep4ForValue2
    '...
    Case CriterionValue#: StatementFromStep4ForValue#
    Case Else: StatementFromStep4ForElse
End Select


Get immediate free access to the Excel VBA Change Font Color Based on Cell Value workbook example

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:

  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
'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


Get immediate free access to the Excel VBA Change Font Color Based on Cell Value workbook example

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):

  1. A reference to the applicable workbook.
  2. A reference to the applicable worksheet.
  3. 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


Get immediate free access to the Excel VBA Change Font Color Based on Cell Value workbook example

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)


Get immediate free access to the Excel VBA Change Font Color Based on Cell Value workbook example

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.

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

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

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

Select Case Statement Version

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

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vba-font-color-value/
Select Case WorkbookObjectReferenceForValue.WorksheetObjectReferenceForValue.CellRangeObjectReferenceForValue.Value
    Case CriterionValue1: StatementFromStep4ForValue1
    Case CriterionValue2: StatementFromStep4ForValue2
    '...
    Case CriterionValue#: StatementFromStep4ForValue#
    Case Else: StatementFromStep4ForElse
End Select

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

'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


Get immediate free access to the Excel VBA Change Font Color Based on Cell Value workbook example

Step 4 Example

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

'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

(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:

  1. vbBlack.
  2. vbRed.
  3. vbGreen.
  4. vbYellow.
  5. vbBlue.
  6. vbMagenta.
  7. vbCyan.
  8. 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.

Example: Excel VBA change font color based on cell value with macros


Get immediate free access to the Excel VBA Change Font Color Based on Cell Value workbook example

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.


Get immediate free access to the Excel VBA Change Font Color Based on Cell Value workbook example

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 above.

Related Excel Macro and VBA Training Materials and Resources

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

  • Tutorials about general macro and VBA constructs and structures:
    • Tutorials for Beginners:
      • Excel Macros: Click here to open.
      • Excel VBA: Click here to open.
    • Enable macros in Excel: Click here to open.
    • Work with the Visual Basic Editor (VBE): Click here to open.
    • Create Sub procedures: Click here to open.
    • Refer to objects (click here to open), including:
      • Sheets: Click here to open.
      • Cell ranges: Click here to open.
    • Work with:
      • Properties: Click here to open.
      • Variables: Click here to open.
      • Data types: Click here to open.
      • Functions: Click here to open.
      • Events: Click here to open.
      • R1C1-style references: Click here to open.
      • The Select Case statement: Click here to open.
      • Loops: Click here to open.
  • Tutorials with practical VBA applications and macro examples:
    • Activate a workbook: Click here to open.
    • Find the last row and last column: Click here to open.
    • Work with the Value property: Click here to open.
    • Check if a cell is empty: Click here to open.
    • Work with font characteristics: Click here to open.
    • Work with Excel's AutoFilter: Click here to open.
    • Search and find: Click here to open.

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

  • Excel VBA Font Color Index in 2 Easy Steps: Click here to open.
  • Excel VBA Font Color RGB in 2 Easy Steps: Click here to open.
  • Excel VBA Font Color HEX in 5 Easy Steps: Click here to open.
  • Excel VBA Change Font Color for Part of Text in 8 Easy Steps: Click here to open.
  • Excel VBA Text Box Font Color in 2 Easy Steps: Click here to open.
  • Excel VBA ActiveX Text Box Font Color in 2 Easy Steps: Click here to open.
  • Excel VBA UserForm Text Box Font Color in 2 Easy Steps: Click here to open.
  • Excel VBA Chart Data Label Font Color in 4 Easy Steps: Click here to open.
  • Excel VBA ActiveX Label Font Color in 2 Easy Steps: Click here to open.
  • Excel VBA UserForm Label Font Color in 2 Easy Steps: Click here to open.

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:

  • Premium Courses at the Power Spreadsheets Academy: Click here to open.
  • Books at the Power Spreadsheets Library: Click here to open.
  • VBA Cheat Sheets: Click here to open.

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).

guest
guest
1 Comment
Most Voted
Newest Oldest
Inline Feedbacks
View all comments

I publish a lot of Tutorials and Training Resources about Microsoft Excel and VBA. Here are some of my most popular Excel Training Resources:

  1. Free Excel VBA Email Course
  2. Excel Macro Tutorial for Beginners
  3. Excel Power Query (Get and Transform) Tutorial for Beginners
  4. Excel Keyboard Shortcut Cheat Sheet
  5. Excel Resources
Terms and Conditions

Privacy Policy

Limit of Liability and Disclaimer of Warranty

Affiliate Disclosure

Copyright © 2015–2023 PDS Intelligence Pte. Ltd. All rights reserved.
Excel ® is a registered trademark of the Microsoft Corporation. Power Spreadsheets is not affiliated with the Microsoft Corporation.