Here’s an overview of coloring cells based on various conditions.

**Excel Formula to Color Cell If the Value Follows a Condition: 3 Approaches**

We’ve got a data set with the Names, Book Types, and Prices of some books of a book shop. We’ll color the cells based on various conditions.

**Method 1 – Excel Formula to Color Cells of a Column Based on a Specific Value**

Let’s color the cells of the column Book Type if the book is a novel.

**Steps:**

- Select the column you want to color (without the Column Header). We have selected the column Book Type (
**C5:C14**).

- Go to the
**Home**tab and select**Conditional Formatting**. - Click on
**New Rule**.

- A dialog box called
**New Formatting Rule**will open. - Click on
**Use a formula to determine which cells to format**.

- In the box called
**Format values where this formula is true,**enter any of the following formulas:

`=C5="Novel"`

OR

`=$C5="Novel"`

**⧪**** Notes**

- To color a single column based on some condition using
**Conditional Formatting**, use the Relative Cell Reference or the Mixed Cell Reference (lock the column, not the row) of the first cell inside the formula. **C4**is the first cell of the column Book Type. We can use either**C4**or**$C4**, but not**C$4**or**$C$4**.- And the text “Novel” is my criteria. We want to find out the cells whose values are equal to the text “Novel”.

- Click on
**Format**. A dialog box called**Format Cells**will open.

- Select your desired color from the tab
**Fill**. We have selected light brown.

- Click on
**OK**. You will be directed back to the**New Formatting Rule**box. - Click on
**OK**again. - You will find the cells of your selected column that fulfill the criteria marked in your chosen color.

**⧪**** Additional Example:**

You can also color the cells of the column Price that maintain a condition in this way. Let’s color the cells that have a price greater than or equal to $25.00. The steps are all the largely the same with the following exceptions:

- Select the column Price when choosing the columns.

- In the New Formatting Rule box, replace the formula with this:

`=D5>=25 `

Or

`=$D5>=25`

- You will get the cells that contain a price greater than or equal to $25.00 marked in your chosen color.

You can use the same method to color cells of a single column that are less than a particular value. Use the less-than sign (<) in the formula.

**Method 2 – Excel Formula to Color Cells of a Column If It Contains Specific Text**

We’ll color the Book Names with the text “History” in them. The steps are all the same as in Method 1. The changes are:

- Select the column Book Name.

- For Conditional Formatting New Rule, you can use different formulas for case-sensitive matches and case-insensitive matches.

**⧪**** Formulas for Case-Sensitive Match:**

`=IF(ISERROR(FIND("History",B5)),FALSE,TRUE)`

OR

`=IF(ISERROR(FIND("History",$B5)),FALSE,TRUE)`

- You will get the books that have the text “History” in their names marked in your desired color.

**Explanation of the Formula**

**FIND(“History”,B4)**returns an integer if it finds the text “History” (case-sensitive match) inside cell**B4**. Otherwise, returns a value error.- Let cell
**B4**doesn’t contain the text “History”. So, now the formula becomes**IF(ISERROR(#VALUE!),FALSE,TRUE).** - The
**ISERROR**function returns a TRUE if it finds an error. So, now the formula becomes**IF(TRUE,FALSE,TRUE).** - The
**IF**function returns the 2nd argument if the 1st argument is TRUE. Otherwise, returns the 3rd argument. - Therefore, it will return the 2nd argument, FALSE.
- Similarly, if cell
**B4**had contained the text “History”, the formula would have returned TRUE.

**Note**

This formula works for a case-sensitive match. So “history” or “HISTORY” in place of “History” won’t work.

**⧪**** Formulas for Case-Insensitive Match:**

`=IF(ISERROR(SEARCH("history",B4)),FALSE,TRUE)`

OR

`=IF(ISERROR(SEARCH("history",$B4)),FALSE,TRUE)`

- You will get the books that have the text “History” or “history” or “HISTORY” or so on in their names marked in your desired color.

**Explanation of the Formula**

**SEARCH(“history”,B4)**returns an integer if it finds the text “history” (case-insensitive match) inside cell**B4**. Otherwise, returns a value error.- Let the cell
**B4**doesn’t contain the text “history”. So, now the formula becomes**IF(ISERROR(#VALUE!),FALSE,TRUE).** - The
**ISERROR**function returns a TRUE if it finds an error. So, now the formula becomes**IF(TRUE,FALSE,TRUE).** - The
**IF**function returns the 2nd argument if the 1st argument is TRUE. Otherwise, returns the 3rd argument. - Therefore, it will return the 2nd argument, FALSE.
- Similarly, if cell
**B4**had contained the text “history”, the formula would have returned TRUE.

**Note**

This formula works for a case-insensitive match. So “History” or “HISTORY” in place of “history” will also work.

**Method 3 – Excel Formula to Color Cells in Multiple Columns Depending on the Value of a Single Column**

We’ll color the columns Book Name and Book Type depending on the values of the column Book Type. We’ll color a book only if it’s a Novel. The steps are the same as in Method 1, with the following exceptions:

- Select both of the columns when applying Conditional Formatting.

- Use the following formula with the Mixed Cell Reference in the formula box for the New Formatting Rule dialog.

`=$C5="Novel"`

- Choose your desired color from the
**Format Cells**dialogue box. - Click
**OK**twice. You will get the cells in both the columns marked in your chosen color if the book is a novel.

**⧪**** Additional Example:**

You can also color the cells of the column Book Name and Price if the price is greater than or equal to $25.00 in this way:

- Select the two columns Book Name and Price.
- Open Conditional Formatting and New Rule.

- Enter this formula with a Mixed Cell Reference:

**=$D5>=25**

- Choose your desired color from the
**Format Cells**dialogue box. - Click
**OK**twice. You will get the cells in both the columns marked in your chosen color if the price is greater than or equal to $25.00.

**Things to Remember**

- To color the cells of a single column, you can use either the Relative Cell Reference or the Mixed Cell Reference (Locking the Column) in the formula.
- To color the cells of multiple columns based on a single column, you must use the Mixed Cell Reference (Locking the Column) in the formula.
- The
**FIND**function goes for a case-sensitive match, and the**SEARCH**function goes for a case-insensitive match.

**Frequently Asked Question**

**Why must we use Mixed Cell Reference while coloring cells of multiple columns based on a single column?**

When we select multiple cells and apply a formula to them through **Conditional Formatting**, the formula is applied to the first cell of the selected range.

Then, it’s copied to the rest of the selected cells, just like we copy a formula in our worksheet from one cell to another by dragging the Fill Handle.

Let’s understand it through an example.

When we apply the formula **=C4=”Novel” **to the selected range **B4:C13 **through **Conditional Formatting**, cell **B4** gets the formula **=C4=”Novel”.**

Then cell **B5** gets **=C5=”Novel”.**

Cell **B6** gets **=C6=”Novel”.**

This is OK. For column **B**, there will be no problem.

But when we go to column **C**, cell **C4** will get the formula **=D4=”Novel”. **Because the formula will be copied rightwards.

This is not what we want. We want to color cell **C4** based on the value of cell **C4**, that whether it contains “Novel” or not.

So, we want the formula **=C4=”Novel” **in cell **C4**, not **=D4=”Novel”.**

That’s why we need to insert the Mixed Cell Reference. If we use **=$C4=”Novel”, **it will remain **=C4=”Novel” **when it is copied from column **B** to column **C**.

But we lock only the column, not the row, because we want the cell reference to increase by 1 when it is copied downwards.

**Download the Practice Workbook**

## Related Articles

- Uses of CELL Color A1 in Excel
- How to Color Code Cells in Excel
- VBA to Change Cell Color Based on Value in Excel
- How to Change Cell Color Based on a Value in Excel
- How to Fill Color in Cell Using Formula in Excel
- How to Fill Cell with Color Based on Percentage in Excel
- How to Change Text Color with Formula in Excel
- How to Apply Formula Based on Cell Color in Excel

**<< Go Back to Color Cell in Excel | Excel Cell Format | Learn Excel**