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.

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.

## 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: **How to Do Conditional Formatting Based on Another Cell in Excel

## 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:** Conditional Formatting Based on Multiple Values of Another Cell

## 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

## 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

## 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.

## 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.

## 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.

## 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.

## 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:** How to Apply Conditional Formatting for Blank Cells in Excel

## 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.

## 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

## 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*.

**Download Workbook**

## 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.

## Further Readings

- VBA Conditional Formatting Based on Another Cell Value in Excel
- Change Font Color Based on Value of Another Cell in Excel
- How to Apply Conditional Formatting to the Selected Cells in Excel

**<< Go Back to Conditional Formatting with Multiple Conditions | Conditional Formatting | Learn Excel**