Formatting Issues

How to Highlight Excel Cells Based on Values in Another Column

Maira Mehtab Sep 22, 2026 872 views

Question details

The user needs to dynamically format prices in one column to be green or red depending on whether they meet, exceed, or fall below minimum target prices in another column across hundreds of rows.

Product: Excel
Device & OS: not provided
Scenario: Comparing numeric data between two columns and applying color-coded visual cues (green for target met/exceeded, red for missed target) based on the comparison results.
Observed behavior: Cells need to automatically change color based on custom logical rules evaluating values against a corresponding target column.

Before you start

Ensure that the data in both columns contains valid numbers and that there are no hidden text characters or extra spaces, as these can cause conditional formatting rules to fail.

Solution 1Recommended

Use Custom Formula Rules in Conditional Formatting

Create conditional formatting rules using logical formulas to compare values row-by-row and apply the desired colors.

By using the AND and ISNUMBER functions in your formatting rules, you ensure that the comparison only applies to cells containing actual numbers, preventing blank cells or text headers from being incorrectly formatted.

Select the target range

Highlight the entire column or the specific range of cells in the first column (e.g., column I) that you want to apply the color formatting to.

Open Conditional Formatting

Go to the 'Home' tab on the Excel ribbon, click 'Conditional Formatting', and select 'New Rule' from the dropdown menu.

Create the rule for green formatting

Select 'Use a formula to determine which cells to format'. In the formula box, enter =AND(I1>=T1,ISNUMBER(I1),ISNUMBER(T1)). Click 'Format', navigate to the 'Fill' tab, select a green color, and click 'OK' twice.

Create the rule for red formatting

Keep the same range selected and open 'New Rule' again. Use the formula =AND(I1<T1,ISNUMBER(I1),ISNUMBER(T1)). Click 'Format', choose a red fill color, and click 'OK' twice.

Verify the applied rules

Go to 'Conditional Formatting' and select 'Manage Rules' to ensure the 'Applies to' range is correct for both rules. Click 'Apply' to see the changes in your spreadsheet.

Adjusting Row References: If your data starts on a row other than row 1 (for instance, row 2 due to header rows), make sure to update the row numbers in your formula to match the first row of your selection (e.g., =AND(I2>=T2,ISNUMBER(I2),ISNUMBER(T2))).

Effortless Data Visualization

Easily Highlight Cells Conditionally in WPS Spreadsheet

WPS Spreadsheet provides an intuitive Conditional Formatting tool that makes comparing data across columns seamless. It fully supports custom formulas and is highly optimized for processing formatting rules across thousands of rows quickly.

1. Select your column: Open your file in WPS Spreadsheet and select the column you want to format.
2. Add a new rule: Go to the Home tab, click 'Conditional Formatting', and choose 'New Rule'.
3. Input the formula: Select the formula option, enter your comparison logic, set your preferred fill color, and click OK to apply.

100% compatible with Microsoft Excel (.xlsx) conditional formatting rules and formulasUser-friendly interface for managing multiple formatting rules simultaneouslyHigh performance and lightweight, ensuring no lag when formatting large datasetsFree to use for personal and daily office tasks

microsoft office alternative - wps office

Frequently Asked Questions

Why is my conditional formatting highlighting the wrong rows?

This typically occurs when the cell reference in your formula does not match the active cell of your selected range. If you select range I2:I100, your formula must start referencing row 2 (e.g., =I2>=T2), not row 1.

Can I apply conditional formatting based on text instead of numbers?

Yes. Instead of using the ISNUMBER function, you can use exact text comparisons in your formula, such as =A1="Completed", to trigger the formatting rules.

How do I copy these formatting rules to other columns?

You can use the Format Painter tool. Select a cell that already has your conditional formatting rules applied, click the Format Painter icon on the Home tab, and then click and drag over the new column to apply the same rules.

Will this formatting update automatically if the target prices change?

Yes, conditional formatting is dynamic. If you update a target price in column T, the formatting in column I will automatically recalculate and change colors instantly based on your new data.

Document & File Issues