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

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


Similar Articles to Explore

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

Get FREE Advanced Excel Exercises with Solutions!
Asikul Himel
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
  1. 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
      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

  2. 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?

    • 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

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo