If you are looking for some of the easiest ways to do **Conditional Formatting** based on another cell range in Excel, then you are in the right place.

Sometimes it becomes necessary to highlight a cell range based on another cell range for some conditions while working with a large dataset in **Microsoft Excel**. This article will help you to know the ways of doing **Conditional Formatting** based on another cell range.

## Download Workbook

## 12 Ways to Use Conditional Formatting Based On Another Cell Range in Excel

Here, I have the two data tables to demonstrate the ways of **Conditional Formatting **based on another cell range in Excel. The first table has *Sales* value for different years for some products and the second table contains ** Order Date, Delivery Date **and

**for some items of a company.**

*Sales*For this purpose, I have used** Microsoft Excel 365 **version, you can use any other versions according to your convenience.

__Method-1__: Conditional Formatting Based On Another Cell Range for Equal to Operator

Here, we will highlight the cells of the **Sales of 2019 **column based on the cell ranges of the **Sales of 2018 **column. For **Conditional Formatting**, the condition would be that the cells of the **Sales of 2019 **column will be *Equal to* the cell ranges of the **Sales of 2018 **column.

** Step-01**:

➤Select the cell range on which you want to apply the

**Conditional Formatting**(Here, I have selected the column

**Sales of 2019**)

➤Go to

**Home**Tab>>

**Conditional Formatting**Dropdown>>

**New Rule**Option.

Then the **New Formatting Rule **Wizard will appear.

➤Select **Use a formula to determine which cells to format** option.

➤Click on **Format **Option.

After that, the **Format Cells **Dialog Box will open up.

➤Select **Fill **Option.

➤Choose any **Background Color.**➤Click on

**OK**.

After that, the **Preview **Option will be shown as below.

** Step-02**:

➤Write the following formula in the

**Format values where this formula is true:**Box

`=$D5=$C5`

When the cells of **Column D** will be *Equal to* the corresponding cells of **Column C**, then the **Conditional Formatting **will appear in that cell of **Column D**.

➤Press **OK**.

** Result**:

After that, you will get the cells of the

**Sales of 2019**column highlighted whose values are

*Equal to*the corresponding cells of the cells range of the

**Sales of 2018**.

**Read more:** **Conditional Formatting Based On Another Cell in Excel**

__Method-2__: Conditional Formatting Based On Another Cell Range for Not Equal to Operator

You can apply **Conditional Formatting **to the cells of the **Sales of 2019 **column by using ** Not Equal to Operator**.

** Step-01**:

➤Follow

**Step-01**of

**Method-1**.

After that, you will get the following

**New Formatting Rule**Dialog Box.

➤Type the following formula in the **Format values where this formula is true: **Box

`=$D5<>$C5`

When the cells of **Column D** will be* Not Equal to* the corresponding cells of **Column C**, then the **Conditional Formatting **will appear in that cell of **Column D**.

➤Press **OK**.

** Result**:

Afterward, you will get the cells of the

**Sales of 2019**column highlighted whose values are

*Not Equal to*the corresponding cells of the cells range of the

**Sales of 2018**.

**Read more:** **Excel Conditional Formatting Based on Multiple Values of Another Cell**

__Method-3__: Conditional Formatting Based On Another Cell Range for Greater than Operator

Now, we will highlight the cells of the **Sales of 2019 **column which will be *Greater than *the corresponding cell ranges of the **Sales of 2018 **column.

** Step-01**:

➤Follow

**Step-01**of

**Method-1**.

Then, you will get the following

**New Formatting Rule**Dialog Box.

➤Type the following formula in the **Format values where this formula is true: **Box

`=$D5>$C5`

When the cells of **Column D** will be* Greater than* the corresponding cells of **Column C**, then the **Conditional Formatting **will appear in that cell of **Column D**.

➤Press **OK**.

** Result**:

In this way, you will get the cells of the

**Sales of 2019**column highlighted whose values are

*Greater than*the corresponding cells of the cells range of the

**Sales of 2018**.

**Read more:** **Excel Highlight Cell If Value Greater Than Another Cell**

__Method-4__: Using Less than Operator for Conditional Formatting Based On Another Cell Range

You can apply **Conditional Formatting **to the cells of the **Sales of 2019 **column by using *Less than* Operator.

** Step-01**:

➤Follow

**Step-01**of

**Method-1**.

After that, you will get the following

**New Formatting Rule**Dialog Box.

➤Type the following formula in the **Format values where this formula is true: **Box

`=$D5<$C5`

When the cells of **Column D** will be* Less than* the corresponding cells of **Column C**, then the **Conditional Formatting **will appear in that cell of **Column D**.

➤Press **OK**.

** Result**:

Afterward, you will get the cells of the

**Sales of 2019**column highlighted whose values are

*Less than*the corresponding cells of the cells range of the

**Sales of 2018**.

**Read more:** **How to Compare Two Columns Using Conditional Formatting in Excel**

__Method-5__: Conditional Formatting Based On Another Cell Range for Greater than Or Equal to Operator

Here, we will highlight the cells of the **Sales of 2019 **column which will be *Greater than or Equal to *the corresponding cell ranges of the **Sales of 2018 **column.

** Step-01**:

➤Follow

**Step-01**of

**Method-1**.

Then, you will get the following

**New Formatting Rule**Dialog Box.

➤Type the following formula in the **Format values where this formula is true: **Box

`=$D5>=$C5`

When the cells of **Column D** will be* Greater than or Equal to* the corresponding cells of **Column C**, then the **Conditional Formatting **will appear in that cell of **Column D**.

➤Press **OK**.

** Result**:

Afterward, you will get the cells of the

**Sales of 2019**column highlighted whose values are

*Greater than or Equal to*the corresponding cells of the cells range of the

**Sales of 2018**.

__Method-6__: Using Less than Or Equal to Operator for Conditional Formatting Based On Another Cell Range

You can apply **Conditional Formatting **to the cells of the **Sales of 2019 **column by using ** Less than or Equal to **Operator.

** Step-01**:

➤Follow

**Step-01**of

**Method-1**.

After that, you will get the following

**New Formatting Rule**Dialog Box.

➤Type the following formula in the **Format values where this formula is true: **Box

`=$D5<=$C5`

When the cells of **Column D** will be* Less than or Equal to* the corresponding cells of **Column C**, then the **Conditional Formatting **will appear in that cell of **Column D**.

➤Press **OK**.

** Result**:

Then, you will get the cells of the

**Sales of 2019**column highlighted whose values are

*Less than or Equal to*the corresponding cells of the cells range of the

**Sales of 2018**.

**Similar Readings:**

**How to Compare Two Columns in Excel For Finding Differences****Do Conditional Formatting for Multiple Conditions (8 Ways)****Conditional Formatting Multiple Text Values in Excel (4 Easy Ways)****How to Change a Row Color Based on a Text Value in a Cell in Excel**

__Method-7__: Conditional Formatting for Multiple Conditions Using AND Function

While dealing with multiple conditions you can use the **AND** **function**, this will make sure that all the conditions are fulfilled.

Let’s say, you want to highlight the cells of the **Sales of 2019 column** whose values are greater than the corresponding values of the cells of the **Sales of 2018 column **and the **Sales of 2020 column**, then you can use **the AND function.**

** Step-01**:

➤Follow

**Step-01**of

**Method-1**.

After that, you will get the following

**New Formatting Rule**Dialog Box.

➤Type the following formula in the **Format values where this formula is true: **Box

`=AND($D5>$C5,$D5>$E5)`

When the cells of **Column D** will be* Greater than* the corresponding cells of **Column C** and **Column E**, then the **Conditional Formatting **will appear in that cell of **Column D**.

➤Press **OK**.

** Result**:

Then, you will get the cells of the

**Sales of 2019**column highlighted whose values are

*Greater than*the corresponding cells of the cells range of the

**Sales of 2018**and

**Sales of 2020**.

__Method-8__: Conditional Formatting for Multiple Conditions Using OR Function

You can use the **OR function** as well to deal with multiple conditions, unlike **AND** this function will trigger the operation if any of the criteria meet.

For highlighting the cells of the **Sales of 2019 column** whose values are greater than the corresponding values of the cells of the **Sales of 2018 column **or the **Sales of 2020 column**, then you can use the **OR function**.

** Step-01**:

➤Follow

**Step-01**of

**Method-1**.

After that, you will get the following

**New Formatting Rule**Dialog Box.

➤Type the following formula in the **Format values where this formula is true: **Box

`=OR($D5>$C5,$D5>$E5)`

When the cells of **Column D** will be* Greater than* the corresponding cells of **Column C** or **Column E**, then the **Conditional Formatting **will appear in that cell of **Column D**.

➤Press **OK**.

** Result**:

Afterward, you will get the cells of the

**Sales of 2019**column highlighted whose values are

*Greater than*the corresponding cells of the cells range of the

**Sales of 2018**or

**Sales of 2020**.

__Method-9__: Conditional Formatting Based On Another Cell Range for Empty Cells

If you want to highlight the *Order Dates *corresponding to the *Delivery Dates *which are empty which means not delivered yet, then you can follow this method.

** Step-01**:

➤Follow

**Step-01**of

**Method-1**(Here, I have changed the

**Background Color**and selected the

*Order Date*column for

**Conditional Formatting**)

After that, you will get the following **New Formatting Rule **Dialog Box.

➤Type the following formula in the **Format values where this formula is true: **Box

`=$D5=""`

When the cells of **Column D** will be* Equal to ***Blank**, the **Conditional Formatting** will appear to the corresponding cells of **Column C**.

➤Press **OK**.

** Result**:

Then, you will get the cells of the

**Order Date**column highlighted when the corresponding cells of the column

**Delivery Date**will be empty.

**Read more:** **Conditional Formatting for Blank Cells in Excel**

__Method-10__: Conditional Formatting Based On Another Cell Range for Non-Empty Cells

For highlighting the *Order Dates *corresponding to the *Delivery Dates *which are non-empty, then you can follow this method.

** Step-01**:

➤Follow

**Step-01**of

**Method-1**

After that, you will get the following

**New Formatting Rule**Dialog Box.

➤Type the following formula in the **Format values where this formula is true: **Box

`=$D5<>""`

When the cells of **Column D** will be* Not Equal to ***Blank**, the **Conditional Formatting** will appear to the corresponding cells of **Column C**.

➤Press **OK**.

** Result**:

Then, you will get the cells of the

**Order Date**column highlighted when the corresponding cells of the column

**Delivery Date**will be non-empty.

__Method-11__: Conditional Formatting Based On Another Cell Range for Texts

If you want to highlight the cells of the **Sales column **for the corresponding cells of the **Item column **where *Jacket2* (or any other item name) presents, then you can follow this method.

** Step-01**:

➤Follow

**Step-01**of

**Method-1**(Here, I have selected the

*Sales*column for

**Conditional Formatting**)

After that, you will get the following

**New Formatting Rule**Dialog Box.

➤Type the following formula in the **Format values where this formula is true: **Box

`=$B5="Jacket2"`

When the cells of **Column B** will be* Equal to ***“Jacket2”**, the **Conditional Formatting** will appear to the corresponding cells of **Column E**.

➤Press **OK**.

** Result**:

Then, you will get the cells of the

**Sales**column highlighted when the corresponding cells of the column

**Item**will have

*Jacket2*.

**Read more:** **Excel Conditional Formatting with Formula If Cell Contains Text**

__Method-12__: Conditional Formatting Using SEARCH Function for Texts

If you want to highlight the cells of the **Sales column **for the corresponding cells of the **Item column **where ** Jacket** (or any other item name) presents, then you can use the

**SEARCH function**.

** Step-01**:

➤Follow

**Step-01**of

**Method-1**

After that, you will get the following

**New Formatting Rule**Dialog Box.

➤Type the following formula in the **Format values where this formula is true: **Box

`=SEARCH("Jacket",$B5)>0`

When the cells of **Column B** will be* Equal to *the **“Jacket”**, the **Conditional Formatting** will appear to the corresponding cells of **Column E**.

➤Press **OK**.

** Result**:

After that, you will get the cells of the

**Sales**column highlighted when the corresponding cells of the column

**Item**will have an item name starting with

*Jacket*.

## Practice Section

For doing practice by yourself we have provided a** Practice** section like below in a sheet named **Practice**. Please do it by yourself.

## Conclusion

In this article, I tried to cover the easiest ways to do **Conditional Formatting **based on another cell range in Excel effectively. Hope you will find it useful. If you have any suggestions or questions, feel free to share them with us.