Excel Formula to Change Cell Color Based on Text

In our daily life, we use color to communicate a wide range of messages and emotions. In professional life, for example, assume that you have a list of sales leads and need to prioritize them quickly, you can use an Excel formula to change cell color based on text or value or you can use conditional formatting to highlight any leads with the words “urgent” or “priority” in red. This will help you focus on the most important leads and increase your chances of closing more deals.

We use color in different aspects of our professional and business life, such as data analysis, communication, navigation, aesthetics, etc. There are countless ways to use color. One of the ways is to use an Excel formula to change cell color based on text.

To bring clarity and quick identification it is an efficient method to use the Excel formula to change cell color based on text. It helps the reader, businessman, investor, or data analyst to quickly jump to the point.

Excel Formula to Change Cell Color Based on Text: 2 Easy Ways

You can make your Excel data more visually appealing and easier to understand only if you know the tricks. In this article, We will help you to learn 2 simple ways to use Excel formula to change color based on text.

We will use the following dataset to demonstrate the process. Imagine you work for a retail company that tracks inventory in an Excel spreadsheet. You need to find out which stores are low on a product to prevent stockouts.

1. Applying Conditional Formatting

There are two ways we can apply conditional formatting to change color based on text. Depending on the user demand and situation the use and function varies. We will demonstrate both ways.

1.1. Application of Cell Rules

The conditional format has its default features to make the work of the users easy and comfortable. We can use one of the features to make the task easy and quick. We will highlight cells in Orange that have the text “Stocked” and in light red if they have the text “Need Supply”. Check the following steps.

To use conditional formatting, first, select the data range containing the cells you want to format. For example, select the “Status” column from cell range D5:D13.

Next, go to the “Home” tab and find the “Conditional Formatting” drop-down arrow in the “Styles” group. Click on it to open a menu, then select “Highlight Cells Rules” and choose “Text that contains…” from the options.

In the left box of the small window that appears, type or select the text you want to format. For instance, type the text “Supply”.

In the right box, select the color you want to use for formatting by clicking the down arrow. There are a few built-in options, but we will select the down arrow to navigate to the Custom Format.

Then, the “Format Cells” window will open automatically. From there, click on the “Fill” tab and select the orange color. You can also preview the color in the sample box. Finally, click “OK” to confirm the changes.

You will see a preview of the changes in the dataset. If you are satisfied with the result, click “OK” to confirm the changes.

To apply formatting for additional text, follow the same steps as before. Select the range and then go to the Conditional Formatting drop-down arrow in the Style group of the Home tab. Choose “Highlight Cells Rules” and then “Text that contains…” to open the formatting window.

Type “Need Supply” in the left box and select the Custom Format option from the right box. From the Format Cells window, go to the Fill tab and select the red color. Click OK to apply the changes.

Before confirming the changes by clicking “OK,” you can preview them in the dataset. If you don’t like them, pressing “Esc” or “Cancel” will discard them.

After using the Excel formula to change cells based on text, you will have the desired outcome with cells highlighted in orange or red, indicating the stock status of products in your inventory for different locations.

1.2. Creating New Rules

Conditional formatting provides the flexibility to create new rules based on formulas. By using cell references or functions, we can highlight specific data in our dataset. In this case, we want to focus on highlighting the market demand for each product, specifically, the cells that contain the text “Less”.

We will use the following simple steps to get the desired outcome. First, select the data range for the demand column (column E) in the dataset, which typically ranges from E5 to E13.

Then go to the Home tab and click on Conditional Formatting in the Style group.

From the drop-down menu, choose “New Rules” located at the bottom of the list. This opens a new window for creating a formatting rule.

In the New Formatting Rule dialog box, select the “Use a formula to determine which cells to format” option at the bottom of the list.

In the input box, enter the following formula.

=SEARCH("Less",E5:E13)>0

With the help of the SEARCH function, the formula determines if the text “Less” appears in any cell within the range E5 to E13. If it does, the formula evaluates to TRUE (or 1), indicating a match. The formula evaluates to FALSE (or 0) if it doesn’t.

Now, click on the “Format” button to specify the formatting options. In the Format Cells dialog box, go to the Fill tab and choose the light grey color.

Click “OK” to apply the formatting and close the dialog box.

You can preview the changes in the formatting in the preview box.

Finally, click “OK“. Now, you will notice that the cells containing the text “Less” are filled with a light grey color.

Repeat the same process mentioned earlier for the text “Average” and “More”.

For the text “Average”, select the light green color for formatting and use the following formula.

=SEARCH("Average",E5:E13)>0

Again, for the text “More”, select the green color for filling the cell and use the formula listed below.

=SEARCH("More",E5:E13)>0

After completing the process, you will have a result similar to the image shown below, where the cells containing “Less” are highlighted with light grey, “Average” with light green, and “More” with green.

By following these steps, you can easily organize and visually represent your dataset.

2. Employing VBA Code

If you’re an intermediate or advanced user, Microsoft Visual Basic for Applications (VBA) is your superhero tool. If not, you need to enable the Developer tab first to access its mighty powers and change cell colors based on their values.

Let’s start by naming the cell range. Select the cells you want to include in the range, for example, the demand column from E5 to E13. Then go to the Formulas tab and choose the drop-down menu of “Define Name” from the “Defined Names” group.

A window will appear, where you can name the range, such as “Demand”. Click “OK” to save it.

Next, go to the Developer tab and choose “Visual Basic“.

In the VBA window, click “Insert” and then select “Module“.

Now type the following code and save it.

Sub Fill_Color()
Dim cell_value As Range
Dim stat_value As String
Dim rng As Range
Set rng = Range("Demand")
For Each cell_value In rng
stat_value = cell_value.Value
Select Case stat_value
Case "More"
cell_value.Interior.Color = RGB(0, 255, 0)
Case "Average"
cell_value.Interior.Color = RGB(255, 255, 0)
Case "Less"
cell_value.Interior.Color = RGB(255, 0, 0)
End Select
Next cell_value
End Sub

Code Breakdown:

Firstly, we created a Sub procedure named Fill_Color.
After declaring variables, The Set rng = Range(“Demand”) line sets the variable rng to refer to the range named “Demand” in the worksheet. This range will be the target for the color formatting actions performed in the code.
The Select Case statement is used to evaluate the value of the variable stat_value. It allows different code blocks to be executed based on the value of stat_value.
In this code, there are three cases: “More”, “Average”, and “Less”. Each case represents a specific value that stat_value can take. Depending on the value of stat_value, the corresponding code block is executed.
Inside each code block, the Interior.Color property of the current cell (cell_value) is set to a specific RGB color value. This effectively changes the cell’s background color based on the value of stat_value.
“End Select” marks the end of the Select Case statement. “Next cell_value” indicates that it moves to the next cell in the range and continues the loop.
Finally, we ended the sub procedure.

To run the code, click the “Run” button or go to “Macros” under the Developer tab and choose the code you just created. For this example, Fill_Color.

Once executed, you will see that the cells now have to fill colors based on the text they contain.

Change Cell Color Based on Text: Excel Find and Replace Tool

We can also use the find and replace tool to highlight the cells. Check the following steps below.

You can navigate to the find and replace tool using the keyboard shortcut “Ctrl + H” or by going to the Home tab, selecting the down arrow of Find and Replace menu from the Editing group, and clicking on the “Replace” option to open the find and replace dialogue box.

In the Find what box, type “Less” (or the desired text you want to highlight), and in the Replace with box, type the same text “Less”.

On the left side of the Replace with row, click on the Format option to open the formatting options.

Go to the fill option and select the light grey color from the replace format box. You can preview the color in the sample box. Click “OK” to save the settings

Now to make changes in all the cells that have the text “Less”, click on the “Replace All” button.

A notification window will show the outcome (e.g., “We have made 3 replacements.)

Without closing the find and replace menu, you can observe the changes in the dataset.

Repeat the same process to fill cells with the text “Average” using the light green color and cells with the text “More” using the green color respectively.

Once again, you will have an output similar to the image below, where the cells with “Less” are highlighted in light grey, “Average” in light green, and “More” in green.

By following these steps, you can effectively use the find and replace tool to highlight specific text values in your dataset.

NOTE: When using the Find and Replace tool to highlight cells, remember that the fill color remains permanent even if the cell values change. It won’t automatically update based on new values.

Things to Remember

It is important to note that when using conditional formatting for specific text, you need to repeat the process for each unique text individually. Unfortunately, there is no shortcut available for this.
In addition to using functions, you can also directly refer to cells in conditional formatting without using any function.
When using the find and replace option, the cell fill color change becomes somewhat permanent. Even if you change the cell value, the cell fill color will remain unchanged.
Using VBA code is the shortest and easiest method when applying Excel formulas to change cell color based on text.

Frequently Asked Questions

How can I remove conditional formatting from a cell in Excel?

A: To remove conditional formatting from a cell in Excel, select the cell, go to “Conditional Formatting” in the “Home” tab, choose “Clear Rules,” and select “Clear Rules from Selected Cells.”

Can I use an IF statement to change cell color in Excel?

No, an IF statement cannot directly change the color of a cell in Excel. However, you can use an IF statement in conditional formatting to determine when to apply a specific color to a cell.

What are the different conditional formatting options in Excel?

The main conditional formatting options in Excel are:

Highlight Cell Rules: It highlights cells based on specific criteria.
Top/Bottom Rules: For highlighting the top or bottom values in a range.
Data Bars, Color Scales, and Icon Sets: if you want to add visual indicators to cells.

Can I use a custom color for a cell based on its text value?

Yes, you can use a custom color for a cell based on its text value. In the Conditional Formatting dialog box, select “Custom Format,” go to the “Fill” tab, and choose a custom color for the cell.

How can I change the font color based on the cell value in Excel?

To change the font color based on the cell value in Excel, select the cells, go to the Home tab, click on Conditional Formatting, choose “Highlight Cells Rules,” and “Text that Contains.” Then, enter the text value and select the custom format option and go to the Font menu from the Formatting cell dialogue box. Finally, select the font color to apply.

Download Practice Workbook

You can download the workbook, where we have provided a practice section on the right side of each worksheet. Try it yourself.

Formula to Change Cell Color Based on Text.xlsm

Conclusion

In conclusion, Excel formula to change cell color based on text you can use such as formulas and conditional formatting. Each method has its advantages and limitations, but by understanding the different options available, you can easily customize the look of your spreadsheet and make it more visually appealing and easier to read.

By utilizing conditional formatting, you can easily highlight cells that meet specific criteria, such as containing certain text values or meeting thresholds.

You can also use custom colors and apply different formatting options like data bars, color scales, and icon sets. The Find and Replace tool can also be a big help to make your spreadsheet look better.

Additionally, VBA code offers a powerful and efficient way to automate color changes based on text values. With these tools at your disposal, you can enhance data visualization and make your Excel spreadsheets more visually appealing and informative.

Get FREE Advanced Excel Exercises with Solutions!