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. You can 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 VBA and Macro Training Materials and Resources Section below.

Use the following Table of Contents to navigate to the Section you're interested in.

Related Excel VBA and Macro Training Materials and Resources

The following Excel VBA and Macro 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.
    • 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 (series under development).

• 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 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 open.

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.
• 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 open. 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.

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

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. You can 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 VBA and Macro 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).

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 may (among others) 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 may (among others) 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 may (among others) 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”).

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

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

Step 2 Example

The statements inside the Select Case block in the 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

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

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 VBA and Macro 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

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 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 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. You can 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 VBA and Macro Training Materials and Resources Section above.

Related Excel VBA and Macro Training Materials and Resources

The following Excel VBA and Macro 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.
    • 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 (series under development).

• 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 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 open.

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.
• 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 open. 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.