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

We will use the sample dataset below to highlight the profit values based on the value stored in cell H5.

excel highlight cell if value less than another cell


Method 1 – Using Conditional Formatting

In the dataset, the Profit has been calculated from the Cost and Revenue. In the Profit column, we need to highlight the cells whose values are less than the value mentioned in cell H5.

Using Format Only Cells That Contain Option to excel highlight cell if value less than another cell

1.1 Using 'Format Only Cells That Contain' Option

Steps

  • Go to the Home tab > Conditional Formatting > New Rules.

  • An Edit Formatting Rule window will open.
  • Select the Format only cells that contain option.
  • Under Format only cells with:, select Cell Value from the first dropdown menu.
  • In the second dropdown menu, select Less than.
  • In the third dropdown menu, select cell H5.
  • Click on Format.

.

  • In the Format Cells window, go to the Fill tab.
  • Select any color (we picked Yellow) as the fill color.

  • Switch to the Font tab and choose Bold from the Font Style.
  • Set the color as black.
  • Click OK.

  • It 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.

  • Profits below the value in cell H5 will be highlighted in the Profit column.


1.2 Applying Formula

Steps

  • Go to the Home tab > Conditional Formatting > New Rules.

  • The Edit Formatting Rule window will open.
  • Select the Use a formula to determine which cells to format option.
  • In the Format values where this formula is true: box, enter the following formula
=$F5<$H$5
  • Click on Format.

  • In the Format window, go to the Fill tab.
  • Select the Yellow color as the fill color.

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

  • It 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.

  • Profits below the value in cell H5 will be highlighted in the Profit column.

cell value less than the target value are highlighted


1.3 Using 'Less Than' Command

Steps

  • Select the range of cells.
  • Go to the Home tab > Conditional Formatting > Highlight Cell Rules > Less Than.

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

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

  • Switch to the Font tab and choose Bold from the Font Style.
  • Set the color as black.
  • Click OK.

  • It 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.

  • Profits below the value in cell H5 will be highlighted in the Profit column.

cell value less than the target profit is now highlighted.

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


Method 2 – Embedding VBA Code

Steps

  • Go to the Developer tab and click on Visual Basic. If you can’t find the Developer tab, you need to display the Developer tab on the ribbon. You can also press Alt+F11 to open the Visual Basic Editor.

Embedding VBA Code to highlight cell if value less than another cell in Excel

  • In that dialog box, click on Insert > Module.

  • In the Module editor window, add 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

  • Close the Module window.
  • Go to View tab > Macros.
  • Click on View Macros.

  • Select the macros that you created. The file name here is Highlight_Cell_Based_on_Another_Cell. Click Run.

  • An input range box will open, asking for the input range.
  • In that input range box, you need to select the cells that needs to be highlighted.
  • Select the whole Profit column.
  • Click OK.

  • The cell values less than 1200 are now highlighted with yellow color.

Cell value less than the value in cell H5 is now highlighted

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


Download Practice Workbook


Related Articles


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

Get FREE Advanced Excel Exercises with Solutions!
Rubayed Razib Suprov
Rubayed Razib Suprov

Rubayed Razib, holding a BSC degree in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, serves as a devoted member of the ExcelDemy project. He has contributed significantly by authoring numerous articles and showcasing proficiency in VBA. Razib efficiently automates Excel challenges using VBA macros and actively participates in the ExcelDemy forum, providing valuable solutions for user interface challenges. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo