In this article, we will be familiarized with an interesting topic which is conditional formatting based on another text cell in Excel. **Conditional Formatting** makes it easy to highlight data in your worksheets. In this article, we will see how to apply conditional formatting based on **another text cell** in excel in 4 easy ways.

## Download Practice Workbook

Get this sample file to practice by yourself.

## 4 Easy Ways to Apply Conditional Formatting Based on Another Text Cell in Excel

To describe the process, we have prepared a dataset here. The dataset shows the information of the **Annual Transcript** of **7** students with their **Names**, **Obtained Marks** and **Status**.

Following, we inserted the condition** Pass** in **cell C13**.

Now, let’s highlight the dataset based on this condition following the methods below.

### 1. Apply Conditional Formatting with Formula Based on Another Text Cell

In this first process, we will use **the SEARCH function** to apply conditional formatting and find the required text. Let’s see the steps below:

- First, select
**cell range B4:D11**where you want to apply the conditional formatting.

- Now, go to the
**Home**tab and select**Conditional Formatting**.

- Under this drop-down, select
**New Rule**.

- Next, in the
**New Formatting Rule**dialogue box, select**Use a formula to determine which cells to format**. - Here, insert this formula in the
**Format values where this formula is true**box.

`=SEARCH($C$13, B4)>0`

- Following, click on the
**Format**option to open the**Format Cells**dialogue box.

- Here, in the
**Format Cells**dialogue box under the**Fill**option, select the color you want. We can see the color preview in the**Sample**section.

- Lastly, press
**OK**twice to close all dialogue boxes. - Finally, you will get the below result after this.

Here, we used the **SEARCH** function to return the cell text in** C13** inside** cell range B4:D11** and highlight it afterward.

**Note:**You can use this formula

**to highlight only the cells which start with the word “**

`=SEARCH($C$13, B4)>1`

**Pass**” in your database. For example, Pass with distinction or Pass with conditions etc.

### 2. Highlight Entire Row Based on Another Cell Using Excel Formula with Conditional Formatting

Let’s say you want to highlight the names of the students along with the status of the final exam. Let’s work on the students who have passed. Here we will be using the 3 types of formulas to highlight the entire row.

#### 2.1. Apply SEARCH Function

The first option to highlight the entire row is to use the **SEARCH** function. Follow the process below.

- First, select the whole dataset.
- Then, open the
**New Formatting Rule**dialogue box as described in the first method. - Here, insert this formula.

`=SEARCH($C$13,$D4)>0`

- Along with it, change the color from the
**Format**>**Fill**>**OK**option.

- Lastly, again press
**OK**and you will see the result.

Here, we used the **SEARCH** function to return the cell text in** C13** inside** cell D4 **making it unchangeable so that the formula gets repeated throughout this column.

#### 2.2. Use AND Function

Another helpful technique for highlighting the entire row is to apply **the AND function** in excel. Follow the process below.

- First, like the previous method, insert this formula in the
**New Formatting Rule**dialogue box.

`=AND($D5="Pass",$C5>40)`

- Afterward, change the color from the
**Format**>**Fill**tab as described above. - Lastly, press
**OK**and get the final output.

Here, we applied the **AND** function to determine more than one condition at the same time for the selected **cell range B4:D11**.

#### 2.3. Insert OR Function

**The OR function** also works for highlighting the total row based on the cell text.

- In the beginning, select
**cell range B4:D11**. - Then,
**Home**>**Conditional Formatting**>**New Rule**. - Insert this formula in the
**New Formatting Rule**dialogue box.

`=OR($D5="Pass",$C5>40)`

- Next, change the color and press
**OK**. - That’s it, you will see the entire row is highlighted now.

Here, we applied the **OR** function to determine whether at least one condition is true from multiple criteria according to the cell text.

### 3. Insert Data Validation for Conditional Formatting in Excel

**Data Validation** is very interesting in the case of conditional formatting based on another cell. Carefully go through the process.

- In the beginning, select
**cell C13**as we want to imply data here.

- Then, go to the
**Data**and select**Data Validation**under the**Data Tools**group.

- Now, in the
**Data Validation**dialogue box, select**List**as the**Validation criteria**.

- Following, insert the conditions
**Pass**and**Fail**in the**Source**box. - Next, press
**OK**.

- Therefore, you will see that
**cell C13**has the list of conditions to be selected.

- Now, select
**cell range D5:D11**.

- Then, insert this formula in the
**New Formatting Rule**dialogue box like the previous methods.

`=D5=$C$13`

- After that, choose a color from the
**Format**>**Fill**tab and press**OK**>**OK**.

- That’s it, you have got the required output.

- Lastly, for proof checking, change the condition to
**Fail**and the highlighted cells will be changed automatically.

### 4. Excel Conditional Formatting Based on Specific Text

In this last section, let us try the **Specific Text **option to apply conditional formatting. Here are two ways to do this.

#### 4.1. Apply New Formatting Rule

This first one will be done directly from the conditional formatting tab.

- First, go to the
**Data**tab and select**Conditional Formatting**. - Then, select
**Text that Contains**from the**Highlight Cell Rules**section.

- After this, insert
**cell C13**as**Cell Text**.

- Along with it, change the color just like the image below:

- Lastly, press
**OK**and see the final output.

#### 4.2. Use Highlight Cells Rules

Another way is to highlight cells from the **New Formatting Rule** dialogue box.

- In the beginning, select
**Format only cells that contain**as the**Rule Type**.

- Then, choose
**Specific Text**under the**Format only cells with**section.

- Next, insert the cell reference as shown below.

- Lastly, change the highlight color from the
**Format**>**Fill**tab >**OK**.

- That’s it, press
**OK**and you will see the cells are highlighted according to the other cell text.

## Things to Remember

- Before applying conditional formatting, always select the cells where you will apply the condition.
- You can always clear the condition either from a single sheet or the entire workbook with the
**Clear Rules**command in the**Conditional Formatting**tab.

## Conclusion

Hope you will find this article on how to apply **conditional formatting **based on **another text cell **in excel in 4 easy ways very helpful. Tell us if you find any other process. Keep an eye on **ExcelDemy** for more exciting articles like this.

On an Excel sheet, I need a conditional formatting rule to format every cell containing any value that is >=1, in various non-contiguous ranges: (B5:U10,B14:K20,B24:K24,B28:K33,B37:K46,B50:K59,B63:K71,B75:K84,B88:K97,B101:K109,B113:K122,B126:K131,B135:K136), BUT I want the specific formatting of the cells (not the entire rows) that meet that criteria (>=1) within each row, determined by one of three search terms contained within the full text (search term is just a part of the whole text string) of a cell adjacent to each row (in Column A).

As an example (looking at only one row): Within B5:KY5, numerous, but not all, cells contain values greater than or equal to 1. Cell A5 contains text that includes one of my search terms (I’ll call that term “Term1”). Cells within the range of B5:KY5 that have values greater than or equal to one (1) should therefore be formatted according to the search term in A5 (“Term1”).

Any help would be greatly appreciated.

I assume that I will then have to repeat the steps to create Conditional Formatting rules; changing the formatting to coincide with the other two search terms (“Term2” and “Term3”).

Hello Mark,

We have sent you a mail. Can you see it and give us a response?