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.

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.

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.

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.

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.