How do I change the color of a cell based on text value?

Introduction

Changing the color of a cell in Excel based on the text value in that cell is a useful way to visually highlight certain values. For example, you may want to turn cells red if they contain the text “Expired”, or green if they contain the word “Current”.

There are a few different ways to accomplish conditional cell formatting like this in Excel. The main methods are:

– Using the Conditional Formatting feature
– Writing a formula rule
– Using VBA macro code

In this article, we’ll walk through each approach and look at the pros and cons of each. We’ll also look at a few examples to see conditional cell formatting in action.

Using Conditional Formatting

The easiest way to change a cell’s color based on text value is to use Excel’s Conditional Formatting feature. Here’s how:

1. Select the cells you want to format.

2. On the Home tab, click the Conditional Formatting drop-down and select New Rule.

3. In the New Formatting Rule dialog box, select the “Format only cells that contain” option.

4. Under that, select “Specific text”.

5. In the box below, enter the text value you want to trigger the formatting. For example, type “Expired”.

6. Click the Format button and select the Fill tab.

7. Choose the color you want cells with that text to be. Let’s choose red.

8. Click OK to return to the New Formatting Rule dialog.

9. Click OK again to create the rule.

Now any cells in the selected range that contain the text “Expired” will be formatted red.

You can repeat these steps to create multiple rules for different text values. For example, create another rule to turn cells green if they contain “Current”.

The advantage of using Conditional Formatting is it’s very quick and easy to set up. The downside is that it applies just to that worksheet. If you want the same formatting on multiple sheets, you’d need to recreate the rules each time.

Using a Formula

Another approach is to base the formatting on a formula. Here are the steps:

1. Select the cells to format.

2. On the Home tab, click the Conditional Formatting drop-down and choose New Rule.

3. Select “Use a formula to determine which cells to format”.

4. Enter a formula like:

=EXACT($A1,”Expired”)

5. Click Format and choose the Fill and font colors.

6. Click OK to create the rule.

This works by evaluating the formula in each cell. If it returns TRUE, that cell will be formatted. You can use functions like EXACT, SEARCH, FIND, and others to check for text.

Compared to straight conditional formatting, the benefit here is you can reference cells. For example:

=$A1=”Expired”

Checks the text in cell A1 each time.

You can combine multiple formulas with logical operators like AND/OR to create more complex rules.

The downside to this method is it requires more knowledge of Excel formulas. But the advantage is the rules are portable – you can apply the same conditional formatting formula across multiple worksheets by just copying it.

Using VBA Macro Code

The most flexible method for conditional cell formatting based on text is to use a VBA macro.

VBA code allows you to iterate through a range and apply formatting dynamically based on any criteria you define in code.

Here is a simple example to format expired cells red:

“`vb
Sub FormatExpired()

Dim cell As Range

For Each cell In Range(“A1:A10”)
If cell.Value = “Expired” Then
cell.Font.Color = vbRed
End If
Next cell

End Sub
“`

To use this, you would need to open the Visual Basic Editor, paste in the code, and run it.

The advantage of VBA is you can take into account other factors and conditions when formatting cells. For example, you could check the date as well as text value.

The downside is it requires knowledge of VBA code. But the flexibility can be worth it for more complex conditional formatting situations.

Examples

Here are a couple of examples to see conditional cell formatting based on text values in action.

Formatting Expired and Current Cells

In this table, we’ll format the status cells based on their text:

– Expired = Red fill
– Current = Green fill

By applying the conditional formatting rules described earlier, here is the result:

Expired	Current
Expired	Current
Current

The expired statuses stand out in red, while the current ones are green.

Formatting Based on Weekday Name

In this example, we’ll format cells based on their text value matching a weekday name:

To format the cells with a light gray background for Saturday and Sunday, we can create two conditional formatting rules:

1. Format cells containing “Saturday” with Fill color = Light gray

2. Format cells containing “Sunday” with Fill color = Light gray

Applying those rules gives us:

Monday	Saturday
Wednesday	Sunday
Thursday

The weekend days stand out with the gray background.

Conclusion

Changing Excel cell colors based on the text value is easy once you know a few methods:

– Use Conditional Formatting to select text criteria and apply a color
– Write a formula rule to evaluate text and format conditionally
– Use VBA code for maximum flexibility to format cells

Conditional formatting is useful for visualizing data in Excel. Some key examples include highlighting expired statuses, weekends, employee names, departments, and more.

Hopefully this gives you some ideas for how to get started formatting cell colors based on text values in your own worksheets. The techniques can help make important data stand out!