Change Cell Color Based on Text Input in Excel

You may need to change cell colors in a worksheet based on their text content. This formatting can help draw users’ attention to specific values, highlight errors, etc.

In this tutorial, I will show you four methods of changing the color of cells in a worksheet based on the text input in the cell.

Method #1: Using the Find and Replace Feature

You can use the ‘Find and Replace’ feature in Excel to change the color of cells in a worksheet based on their text content.

Suppose you have the following dataset showing various branded electronic items with their product categories.

Dataset to Change Cell Color Based on cell value in Excel

You can change the color of cells in column C containing ‘Dell’ to red using the steps below:

  1. Select the cell range C2:C10 containing the cells whose color you want to change.
Select the range for which you want to change the color
  1. On the ‘Home’ tab, open the ‘Find & Select’ drop-down menu on the ‘Editing’ group and choose the ‘Replace’ option. Alternatively, press the keyboard shortcut ‘Ctrl + H.’
Click the replace option in the find and select drop down
  1. On the ‘Replace’ tab of the ‘Find and Replace’ dialog box, type ‘Dell’ on the ‘Find what’ drop-down and click the second ‘Format’ button.
Enter the text in find what field and click on format
  1. On the ‘Replace Format’ dialog box, choose red on the ‘Background Color’ palette and click OK.
Select the color that you want to apply to the cell
  1. Click ‘Replace All’ on the ‘Find and Replace’ dialog box.
Click on replace all
  1. Click OK on the informational message box that pops up.
Prompt showing how many replacements were done
  1. Close the ‘Find and Replace’ dialog box.

The cells containing ‘Dell’ are now colored red:

Dataset where sell with specific tests have been colored

Note: This method is not dynamic, meaning you must do any updates manually. Furthermore, if you need to apply a different background color to cells containing another text input, you must repeat the formatting and specify the new color.

Also read: Change Cell Color Based on Value of Another Cell in Excel

Method #2: Apply Conditional Formatting Using Options on the Ribbon

Excel’s conditional formatting feature enables you to format cells or ranges of cells based on specific conditions or criteria.

This formatting is dynamic, meaning it automatically changes as the text in the cells changes or when conditions are met.

Suppose you have the following dataset displaying various branded electronic items and their respective product categories.

Dataset to Change Cell Color Based on cell value in Excel

You can use conditional formatting to change the color of cells containing ‘Dell’ in column C to red by applying the steps below:

  1. Select the cell range C2:C10 containing the cells whose color you want to change.
Select the range for which you want to change the color
  1. On the ‘Home’ tab, on the ‘Styles’ group, open the ‘Conditional Formatting’ drop-down list and choose the ‘New Rule’ option.
Click on new rule and conditional formatting drop down
  1. On the ‘New Formatting Rule’ dialog box, do the following:
    1. On the ‘Select a Rule Type’ box, choose the ‘Format only cells that contain’ option.
    2. On the ‘Edit the Rule Description’ box, select ‘Specific Text’ on the first drop-down menu.
    3. Select ‘containing’ on the second drop-down menu.
    4. Type ‘Dell’ on the text box next to the second drop-down menu.
    5. Click the ‘Format’ button.
specify the text in the format only cells with field
  1. On the ‘Fill’ tab of the ‘Format Cells’ dialog box, select red on the ‘Background Color’ palette and click OK.
Select the Change cell color based on cell value and put in Excel
  1. Click OK on the ‘New Formatting Rule’ dialog box.

The cells containing ‘Dell’ will become red:

Dataset where sell with specific tests have been colored

Note: Although this method is dynamic and new cells containing ‘Dell’ will be automatically colored red, you must create a new rule to apply a different background color to cells with a different text input.

Also read: How to Change Font Color Based on Cell Value in Excel?

Method #3: Apply Conditional Formatting Using Quick Analysis Feature

You can use the Quick Analysis feature to change the color of cells in Excel based on text input.

Quick Analysis is a feature in Excel that allows you to analyze and format data quickly.

This feature provides tools and options for performing common tasks without manually navigating through Excel’s various menus and options.

Suppose you have the dataset below displaying various branded electronic items and their respective product categories.

Dataset to Change Cell Color Based on cell value in Excel

You can use Quick Analysis to change the color of cells containing ‘Dell’ in column C to light red by applying the steps below:

  1. Select the cell range C2:C10 and notice a small icon (a small square with a lightning bolt) in the selected data’s bottom-right corner.
Select the range and click on quick analysis icon
  1. Click the Quick Analysis icon or press the keyboard shortcut ‘Ctrl + Q’ and choose the ‘Text that Contains’ option on the ‘Formatting’ tab.
Select the text option in the formatting tab
  1. On the ‘Text That Contains’ dialog box, type ‘Dell’ on the text box and choose ‘Light Red Fill’ on the drop-down menu.
Specify the text and select the formatting

You will notice a preview of the color change on your dataset.

Note: If you don’t want to use the colors from the drop-down menu, select ‘Custom Format’ to open the ‘Format Cells’ dialog box and access more colors.

  1. Click OK.

The cells containing ‘Dell’ are cored light red:

Cell color is changed based on text input

Note: Although this method is dynamic and new cells containing ‘Dell’ will be automatically colored light red, you must repeat the process if you need to apply a different background color to cells having a different text input.

Also read: How to Highlight Every Other Row in Excel (Conditional Formatting & VBA)

Method #4: Use Excel VBA

You can use Excel VBA to change the cell color based on text content. You can create code that simultaneously applies colors to cells containing different text content.

Suppose you have the following dataset displaying various branded electronic items and their corresponding product categories.

Dataset to Change Cell Color Based on cell value in Excel

Before you write the VBA code, name the data range using the steps below:

  1. Select the dataset.
Select the data range in which you want to change cell color based on cell value
  1. On the ‘Formulas’ tab, click ‘Define Name’ on the ‘Defined Names’ group.
Click on define name option in the ribbon
  1. On the ‘New Name’ dialog box, enter a name (in this example, we enter ‘Electronics’) on the ‘Name’ text box and click OK.
Enter the name for the defined name

After naming the data range, create a standard module and enter the following subroutine:

Sub ChangeCellColorBasedOnTextInput()

    ' Declares variables for cell reference, text input, and range
    Dim cellRef As Range
    Dim textInput As String
    Dim rng As Range

    ' Sets the range defined by the name "Electronics"
    Set rng = Range("Electronics")

    ' Iterates through each cell in the range
    For Each cellRef In rng
        ' Retrieves the text value of the current cell
        textInput = cellRef.Value

        ' Changes the cell's background color based on its text value
        Select Case textInput
            Case "Dell"
                cellRef.Interior.Color = RGB(255, 0, 0) ' Red for Dell
            Case "Microsoft"
                cellRef.Interior.Color = RGB(0, 255, 0) ' Green for Microsoft
            Case "Lenovo"
                cellRef.Interior.Color = RGB(255, 255, 0) ' Yellow for Lenovo
        End Select
    Next cellRef

End Sub

When you run the code, cells containing ‘Dell’ are colored red, those with ‘Lenovo’ are yellow, and those with ‘Microsoft’ are light green:

Cells are colored based on the cell value with vba macro

Note: This method is not dynamic; therefore, you must rerun the code when there are changes in the data.

The above VBA code changes the background color of cells within a named range (“Electronics”) based on specific text inputs.

It iterates through each cell in this range and checks the cell’s value. Depending on the text in the cell (e.g., “Dell”, “Microsoft”, “Lenovo”), the subroutine applies a different background color using the RGB function.

For instance, cells containing “Dell” will have a red background, “Microsoft” will have green, and “Lenovo” will be yellow.

This method is useful for visually distinguishing data in a worksheet based on text criteria, enhancing readability, and data segmentation.

In this tutorial, I showed you four methods for changing the color of cells in Excel based on text input.

I hope you found the tutorial helpful.

Other Excel articles you may also like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment