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 Excel. This article will help you to know the ways of doing **Conditional Formatting** based on another cell range.

## Download Workbook

## 12 Ways to Do 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 *Sales *for some items of a company.

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.