How to Highlight Cell If Value Is Less Than Another Cell in Excel

In many instances, the users might urgently need to highlight the cell values that are less than or greater than some other values stored in another cell. If you are curious to know how you can highlight cell values if the value is less than another cell, then this article may come in handy for you. In this article, we discuss how you can highlight cell value in Excel if the value is less than another cell with elaborate explanations.

Highlight a Cell If Value Is Less Than Another Cell in Excel: 2 Ways

For the demonstration purpose, we are going to use the below dataset. We got Product IDs, their cost, revenue, and profit calculated from them. We need to highlight the profit values based on the values stored in cell H5.

1. Using Conditional Formatting

The conditional formatting feature is one of the most powerful tools in Excel, which helps users to format their cell values with minimal effort. Here, we have a column of values that need to be highlighted based on other cell values. It can be accomplished by using the conditional formatting default Less than feature or using the Custom Formula feature or Format cell that contain cell value options.

1.1 Using 'Format Only Cells That Contain' Option

In the conditions formatting window, we can use only cells that contain the rule directly to highlight the cell that meets our criteria.

Steps

• Here, we got the profit calculated from the Cost and the Revenue.
• From these values, we need to highlight only those cells whose values are less than the value mentioned in cell H5.

• To do this, go to the Home tab > Conditional Formatting > New Rules.

• Then, there will be a new Edit Formatting Rule window.
• In that window, select the Format only cells that contain the option.
• Then, in the below first dropdown menu, select Cell Value.
• In the second dropdown menu, select Less than.
• Finally, in the third dropdown menu, select cell H5.
• Click on Format.

.

• In the Format Cells window, go to the Fill tab.
• And then, select the Yellow color as the fill color.

• Then switch to the Font tab and choose Bold from the Font Style.
• Set the color as black.
• Click OK after this.

• After clicking OK, this will take you to the Conditional Formatting Rules Manager window.
• In that window, ensure that the Rule is applicable or applied to the targeted range.
• Click Apply after this.

• After clicking Apply, you will notice that the values in the Profit column, which are less than the value mentioned in cell H5, are now highlighted.

1.2 Applying Formula

The method name is quite self-explanatory here. This option inside the conditional formatting enables us to apply a custom formula of our choice to highlight cells.

Steps

• Here, we got the profit calculated from the Cost and the Revenue.
• From these values, we need to highlight only those cells whose values are less than the value mentioned in cell H5.

• To do this, go to the Home tab > Conditional Formatting > New Rules.

• Then, there will be a new Edit Formatting Rule window.
• In that window, select the Use a formula to determine which cells to format option.
• Then, in the below range box, enter the following formula

• Click on Format.

• In the Format window, go to the Fill tab.
• And then select the Yellow color as the fill color.

• Then switch to the Font tab and choose Bold from the Font Style.
• Set the color as black.
• Click OK after this.

• After clicking OK, this will take you to the Conditional Formatting Rules Manager window.
• In that window, ensure that the Rule is applicable or applied to the targeted range.
• Click Apply after this.

• After clicking Apply, you will notice that the values in the Profit column, which are less than the value mentioned in cell H5, are now highlighted.

• And this is how we highlight the cell value less than another cell value in Excel.

1.3 Using 'Less Than' Command

Compared to previous methods, this allows users to directly implement rules on the cells.

Steps

• Here, we got the profit calculated from the Cost and the Revenue.
• From these values, we need to highlight only those cells whose values are less than the value mentioned in cell H5.

• To do this, first select the range of cells that you wish to format.
• Then go to the Home tab > Conditional Formatting > HIghlight Cell Rules > Less Than.

• Then, in the Less Than window, select cell H5 in the Format cells that are LESS THAN range box.
• Next, in the drop-down menu, select Custom Format.

• In the Format Cells window, go to the Fill tab.
• And then select the Yellow color as the fill color.

• Then switch to the Font tab and choose Bold from the Font Style.
• Set the color as black.
• Click OK after this.

• After clicking OK, this will take you to the Conditional Formatting Rules Manager window.
• In that window, ensure that the Rule is applicable or applied to the targeted range.
• Click Apply after this.

• After clicking Apply, you will notice that the values in the Profit column, which are less than the value mentioned in cell H5, are now highlighted.

• And this is how we highlight the cell value less than another cell value in Excel.

Read More: How to Highlight Cell Using the If Statement in Excel 

2. Embedding VBA Code

Using a small VBA code can make this whole process of highlighting cells if it is less than another cell less time-consuming and hassle-free.

Steps

• First, go to the Developer tab and click on Visual Basic. If you don’t have that, you need to display the Developer tab on the ribbon. You can also press ‘Alt+F11’for open the Visual Basic Editor.

• Then there will be a new dialog box. In that dialog box, click on Insert > Module.

• Next, in the Module editor window, type the following code

Sub Highlight_Cell_Based_on_Another_Cell()
Dim x As Range
On Error Resume Next
Set x = Application.InputBox( _
Title:="Highlight Cell Value Based on Another Celll", _
Prompt:="Select a Range of Cells That Need to be Highlighted", _
Type:=8)
On Error GoTo 0
x.FormatConditions.Delete
x.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
Formula1:=Range("H5").Value
x.FormatConditions(1).Interior.Color = RGB(255, 255, 0)
End Sub

• Then, close the Module window.
• After that, go to View tab > Macros.
• Click on View Macros.

• After clicking View Macros, select the macros that you created just now. The file name here is Highlight_Cell_Based_on_Another_Cell. Then click Run.

• After clicking Run, there will be an input range box asking for the input range.
• In that input range box, the user needs to select the cells that are going to be highlighted.
• Select the whole Profit column.
• Click OK after this.

• After clicking OK, you will notice that the cell values less than 1200 are now highlighted with yellow color.

• And this is how we highlight the cell value less than another cell value in Excel.

Read More: How to Highlight Cells in Excel Based on Value

Download Practice Workbook

Download this practice workbook below.

Conclusion

To sum it up, the issue of how you can highlight a cell if the cell value is less than another cell is answered here by 5 different examples. The VBA Macro method requires prior VBA-related knowledge to understand from scratch.

For this problem, a macro-enabled workbook is available to download where you can practice these methods.

Related Articles

• How to Highlight Cells in Excel but Not Print
• Cells Are Not Highlighting in Excel Formula
• How to Highlight Selected Cells in Excel
• How to Click One Cell and Highlight Another in Excel
• How to Highlight Cells Based on Text in Excel
• Highlight Cells That Contain Text from a List in Excel
• How to Highlight Blank Cells with Conditional Formatting in Excel

<< Go Back to Highlight Cell | Highlight in Excel | Learn Excel