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

While dealing with a huge database you may need to change the row color of some specific cells based on a text value to identify them quickly. Excel has many features to do that. Conditional Formatting is one of them. It is a fascinating way to reduce your workload and it can improve your efficiency. Today in this article, we will demonstrate how to change a row color based on a text value in a cell in Excel.

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


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

Think of a situation where you are given the ID, Name, Region, Rank, and Salary of some Sales Representatives. Now you have to change some row colors based on their names, regions, or salaries. In this section, we will demonstrate 3 different ways to do that.

dataset


1. Changing Row Color Based on Text Value

You can change some specific row’s color based on a text value. Using conditional formatting will make your job a lot easier. You can change the row color for either a single condition or multiple conditions. We will discuss both of them in this method.

1.1 For Single Cell Criteria

Let’s say we have to color the rows that have George’s name in them. To do it, create another table anywhere in the worksheet and insert the name in it. Then follow the steps below.

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

Steps:

  • Firstly, select the entire dataset. In your Home tab, go to Conditional Formatting in Style Group.
  • Then, click on it to open available options and from them click on New Rule.

using conditional formatting

  • Consecutively, the New Formatting Rule dialog box opens. Select Use a Formula to determine which Cells to format to continue.

new formatting rule dialog box

  • Further, in the formula section, insert this formula.
=$C5="George"
  • This formula will compare the dataset cells with the name George.
  • When the value will match, it will color the row.
  • For that, click on the Format option.

fill up new formatting dialog box

  • Moreover, we need to format the matched cells. The format section will help you out. We have chosen the color of the text as Automatic. The fill cells option will help you to color the rows with a specific color. In this case, we chose the Yellow color.

selecting color for conditional formatting

  • Afterward, click OK on the next window to get the result.

preview in new formatting dialog box

  • Finally, our row colors are changed based on a text value in a cell.

changed row color based on text


1.2 For Multiple Cell Criteria

Following the same instructions as discussed in the previous method, we can color rows based on multiple conditions. Consider a case where you have to color the rows that have Asia and rank A in them. Follow the steps below to learn this technique.

Change Row Color Based on a Text Value For Multiple Cell

Steps:

  • Firstly, go to the New Formatting Rule dialog box following the steps we did in Method 1.1.
  • Then, select Use a formula to determine which cells to format.
  • Afterward, write down the formula to specify the cells that contain Asia. The Formula is,
=$D5="Asia"
  • Lastly, select the color format for your matched cells. Click OK to continue

setting new formatting dialog box

  • Finally, the conditional formatting feature successfully colors the rows.

Changed Row Color Based on a Text Value For Single Cell

  • Now, we need to color the rows that contain rank A in them. For that, go to

Home → Conditional Formatting → Manage Rules

adding new conditional formatting

  • After that, the Conditional Formatting Rules Manager window appears. Click New Rule to add another one.

adding new rule

  • Again, set the formula for the second condition. Write down the formula in the formula box.
=$E5="A"
  • Further, set the format and you are good to go.
  • Finally, click OK to change the row color based on multiple conditions.

new formatting rule box

  • Again, press OK in the next window.

final set of rules

  • Thus, the result is here.

output of changing row color based on multiple cells

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


2. Altering Row Color Based on Number Value in Excel

We can change row color based on numbers too. In this given situation, we have to change the row colors with a salary of less than 40,000$.

Change Row Color Based on a Number Value in Excel

Steps:

  • Primarily, Insert the formula in the formula box of the New Formatting Rule which can be opened as in Method 1.1.
=$F5<$H$5
  • Afterward, specify the formatting and click OK to continue.

settinf new formattinf rule box

  • Finally, our job here is done.

Changed Row Color Based on a Number Value in Excel

Read More: How to Highlight Row Using Conditional Formatting


3. Applying Formula to Change Row Color Based on Text Value

You can apply functions to change row color based on a text value. The OR and the AND functions can help you in this situation. Let’s learn those methods.

3.1. Using OR Function

We want to color George or Asia containing rows using the OR function. Insert those texts into your reference table.

Apply Formula to Change Row Color Based on a Text Value

Steps:

  • So, write down the OR Formula in the New Formatting Rule window which can be opened as in Method 1.1.
=OR($C5="George",$D5="Asia")
  • Here, the OR formula will compare the cell values with George and Asia, and then it will color the rows that matched the conditions.
  • Again, select a formatting style according to your preferences.

settinf new formatting rule box

  • Lastly, click OK and your job is done.

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


3.2. Inserting AND Function

The AND function also helps you to change row colors. Here we will apply a new condition. We will change row colors that have both Africa region and B rank in them.

Insert the AND Function to change row color

Steps:

  • Primarily, following the same procedures discussed in Method1.1, go to the New Formatting Rule window and apply the AND formula which is,
=AND($D4="Africa",$E4="B")
  • Then, set the formatting styles and click OK to format the cells.

using AND functiuon in formula of new formattinf rule box

  • Finally, the rows have changed their colors according to the conditions.

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 ($) to block the cells.


Download Practice Workbook

Download this practice book to exercise the task while you are reading this article.


Conclusion

We have discussed three suitable ways to change a row color based on a text value in a cell in Excel. You are most welcome to comment if you have any questions or queries.


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