Rubayed Razib Suprov
In many instances, the users might be in argent need of highlighting 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 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.
Download Practice Workbook
Download this practice workbook below.
2 Ways to Highlight a Cell If Value Is Less Than Another Cell in Excel
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 formattings 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 the use only cells that contains the rule directly to highlight the cell that meets our criteria.
Steps
- Here we got the profit calculated from the Cost and the Revenue.
- And 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.
- And 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
=$F5<$H$5- 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 directly implement rules on the cells.
Steps
- Here we got the profit calculated from the Cost and the Revenue.
- And 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.
- Then in the side 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 Blank Cells with Conditional Formatting 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 have to enable the Developer tab. Or 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.
- Then 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 Blank Cells in Excel VBA (6 Suitable Examples)
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.
Feel free to ask any questions or feedback through the comment section. Any suggestion for the betterment of the Exceldemy community will be highly appreciable.
