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.
You can change the color of cells in column C containing ‘Dell’ to red using the steps below:
- Select the cell range C2:C10 containing the cells whose color you want to change.
- 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.’
- 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.
- On the ‘Replace Format’ dialog box, choose red on the ‘Background Color’ palette and click OK.
- Click ‘Replace All’ on the ‘Find and Replace’ dialog box.
- Click OK on the informational message box that pops up.
- Close the ‘Find and Replace’ dialog box.
The cells containing ‘Dell’ are now colored red:
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.
You can use conditional formatting to change the color of cells containing ‘Dell’ in column C to red by applying the steps below:
- Select the cell range C2:C10 containing the cells whose color you want to change.
- On the ‘Home’ tab, on the ‘Styles’ group, open the ‘Conditional Formatting’ drop-down list and choose the ‘New Rule’ option.
- On the ‘New Formatting Rule’ dialog box, do the following:
- On the ‘Select a Rule Type’ box, choose the ‘Format only cells that contain’ option.
- On the ‘Edit the Rule Description’ box, select ‘Specific Text’ on the first drop-down menu.
- Select ‘containing’ on the second drop-down menu.
- Type ‘Dell’ on the text box next to the second drop-down menu.
- Click the ‘Format’ button.
- On the ‘Fill’ tab of the ‘Format Cells’ dialog box, select red on the ‘Background Color’ palette and click OK.
- Click OK on the ‘New Formatting Rule’ dialog box.
The cells containing ‘Dell’ will become red:
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.
You can use Quick Analysis to change the color of cells containing ‘Dell’ in column C to light red by applying the steps below:
- 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.
- Click the Quick Analysis icon or press the keyboard shortcut ‘Ctrl + Q’ and choose the ‘Text that Contains’ option on the ‘Formatting’ tab.
- On the ‘Text That Contains’ dialog box, type ‘Dell’ on the text box and choose ‘Light Red Fill’ on the drop-down menu.
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.
- Click OK.
The cells containing ‘Dell’ are cored light red:
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.
Before you write the VBA code, name the data range using the steps below:
- Select the dataset.
- On the ‘Formulas’ tab, click ‘Define Name’ on the ‘Defined Names’ group.
- On the ‘New Name’ dialog box, enter a name (in this example, we enter ‘Electronics’) on the ‘Name’ text box and click OK.
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:
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: