How to Change Row Color Based on Text Value in Cell in Excel

Written by Asikul Himel

Last updated: Jul 6, 2024

We’ll use the following simple dataset to showcase how to change row colors based on text values inside cells.

Preview of change a row color based on a text value in a cell in excel

Change the Row Color Based on the Text Value in a Cell in Excel: 3 Suitable Ways

Consider the following dataset with the information for ID, Name, Region, Rank, and Salary of some Sales Representatives. We’ll change some row colors based on the names, regions, or salaries.

dataset

Method 1 – Changing the Row Color Based on Text Value

Case 1.1 – For Single Cell Criteria

We have to color the rows that have George’s name in them. We put that value in a separate cell H5.

change a row color based on a text value in a cell in excel

Steps:

Select the entire dataset.
In your Home tab, go to Conditional Formatting in Style Group.
Click on New Rule.

using conditional formatting

The New Formatting Rule dialog box opens. Select Use a Formula to determine which Cells to format to continue.

new formatting rule dialog box

In the formula section, insert this formula.

=$C5="George"

Click on the Format option.

fill up new formatting dialog box

We have chosen the color of the text as Automatic. Go to the Fill tab and pick a background color. In this case, we chose the Yellow color.
Click OK.

selecting color for conditional formatting

Click OK.

preview in new formatting dialog box

Our row colors are changed based on a text value in a cell.

changed row color based on text

Case 1.2 – For Multiple Cell Criteria

Consider a case where you have to color the rows that have Asia and rank A in them.

Change Row Color Based on a Text Value For Multiple Cell

Steps:

Go to the New Formatting Rule dialog box following the steps of Method 1.1.
Select Use a formula to determine which cells to format.
Insert the following formula:

=$D5="Asia"

Select the color format for your matched cells.
Click OK.

setting new formatting dialog box

The conditional formatting feature successfully colors the rows.

Changed Row Color Based on a Text Value For Single Cell

Go to Conditional Formatting and select Manage Rules.

adding new conditional formatting

The Conditional Formatting Rules Manager window appears. Click New Rule to add another one.

adding new rule

Set the following formula for the second condition:

=$E5="A"

Set the format.

Click OK to change the row color based on multiple conditions.

new formatting rule box

Press OK in the next window.

final set of rules

Here are the results.

output of changing row color based on multiple cells

Read More: How to Change Cell Color Based on Date Using Excel Formula

Method 2 – Altering Row Color Based on a Number Value in Excel

We have to change the row colors for employees with a salary of less than 40,000$.

Change Row Color Based on a Number Value in Excel

Steps:

Go to New Formatting Rule (see Method 1.1).
Insert the following formula:

=$F5<$H$5

Specify the formatting and click OK to continue.

settinf new formattinf rule box

Here are the results.

Changed Row Color Based on a Number Value in Excel

Method 3 – Applying a Formula to Change the Row Color Based on a Text Value

Case 3.1 – Using theOR Function

We want to color rows that contain George or Asia. Insert those texts into your reference table.

Apply Formula to Change Row Color Based on a Text Value

Steps:

Insert the following formula in the New Formatting Rule window.

=OR($C5="George",$D5="Asia")

Select a formatting style according to your preferences.

settinf new formatting rule box

Click OK.

Apply OR Formula to Change Row Color Based on a Text Value

Case 3.2 – Inserting AND Function

We will change row colors that have both Africa for the region and B rank in them.

Insert the AND Function to change row color

Steps:

Go to the New Formatting Rule window and apply the following formula:

=AND($D4="Africa",$E4="B")

Set the formatting style and click OK to format the cells.

using AND functiuon in formula of new formattinf rule box

Here’s the result.

changed row color using AND function

Read More: Conditional Formatting on Multiple Rows Independently in Excel

Things to Remember

You can clear the rules once the formatting is applied

Use the Absolute Cell references ($) for column references to ensure that the conditional formatting is applied on the row.

Download the Practice Workbook

Changing Row Color Based on Text Value in Cell.xlsx

Similar Articles to Explore

<< Go Back to Conditional Formatting Rows | Conditional Formatting | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

SaveSavedRemoved 0

Tags: Conditional Formatting Rows in Excel

Asikul Himel

Asikul Islam Himel, holding a BSc in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology, has contributed over two years to the ExcelDemy project. Starting as an Excel & VBA Content Developer, now he manages projects at You Have Got This Math Project. He wrote 60+ articles for ExcelDemy, reviewed 500+, and focused on quality maintenance. Currently, his responsibilities include project management and team leadership. Himel's interests encompass data analysis, leadership, WordPress applications, and... Read Full Bio

4 Comments

Reply
Iona May 15, 2024 at 3:44 AM

I currently have a formula on my dates to turn red once 7 days overdue. However, if I want to turn the dates back to black once the cell has been filled with green paint, how can I code this in?

Thanks
- Reply
  Lutfor Rahman Shimanto May 15, 2024 at 12:52 PM
  Dear Iona
  
  Thanks for visiting our blog and sharing your requirements! I have reviewed your goal and created an Excel VBA Event procedure (assuming your dates are in column A).
  
  Excel VBA Event Procedure:
```
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim cell As Range

    If Not Intersect(Target, Range("A:A")) Is Nothing Then
        For Each cell In Target
            If cell.Value < (Date - 7) And cell.Interior.Color <> RGB(0, 255, 0) Then
                cell.Font.Color = RGB(255, 0, 0)
            ElseIf cell.Interior.Color = RGB(0, 255, 0) Then
                cell.Font.Color = RGB(0, 0, 0)
            End If
        Next cell
    End If
    
End Sub
```
  Right-click on the sheet name tab, paste the given code into the sheet module and save it. Hopefully, the idea will fulfil your goal; good luck.
  
  Regards
  Lutfor Rahman Shimanto
  Excel & VBA Developer
  ExcelDemy
Reply
Sandy May 17, 2024 at 9:00 PM

Trying to conditionally format a cell to shade when a formula result from another column [@DATE] equals either Saturday or Sunday and it’s the formula in the first column that is blocking that conditional formatting. Can you write conditional formatting based on a cell that is using a formula itself?
- Reply
  Shamima Sultana May 19, 2024 at 12:28 PM
  
  Hello Sandy,
  
  You can use this formula:
  =OR(WEEKDAY([@DATE], 2) = 6, WEEKDAY([@DATE], 2) = 7)
  This checks if the [@DATE] column value is a Saturday (6) or Sunday (7).
  
  Regards
  ExcelDemy

Leave a reply Cancel reply

Submit Excel Problem