We will use the sample dataset below to highlight the profit values based on the value stored in cell H5.
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.
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.
- 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.
Read More: How to Highlight Cells in Excel Based on Value
Download Practice Workbook
Related Articles
- How to Highlight Cells in Excel but Not Print
- How to Highlight Selected Cells 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
Get FREE Advanced Excel Exercises with Solutions!