Formula Errors

Fix Excel Conditional Formatting Formula Not Turning Cells Green

Khadija Khan Sep 28, 2026 871 views

Question details

The user needs to fix an Excel conditional formatting rule that is failing to turn a cell green three days after a specific date is entered.

How to Fix Excel Conditional Formatting Formula Not Turning Cells Green

Product: Excel
Device & OS: not provided
Scenario: Applying a formula-based conditional formatting rule to automate cell highlighting based on elapsed days from an inputted date.
Observed behavior: The target cells do not turn green as expected. The rule appears to be evaluating the wrong cells due to a row reference mismatch, where the formula references row 2 while the target data is in row 84.

Before you start

Check if the dates in your spreadsheet are formatted correctly as true Excel serial dates, as text-based dates will prevent mathematical formulas like TODAY() from working properly.

Solution 1Recommended

Align Formula References with the Applied Data Range

Ensure the row references in your conditional formatting formula exactly match the very first row of the range you selected for the rule.

When you apply a conditional formatting rule to a range (such as I84:I100), Excel evaluates the formula relative to the top-left cell of that range. If your formula references row 2 but your selected range starts at row 84, the formatting engine will evaluate cells far below your target, causing the highlighting to fail or appear on the wrong rows.

Select your target range

Highlight the exact group of cells where you want the green formatting to appear (for example, select I84 down to I100).

Open the Rules Manager

Navigate to the Home tab on the ribbon, click on Conditional Formatting, and select Manage Rules from the dropdown menu.

Edit the rule formula

Select your rule and click Edit Rule. Change the formula so that it references the first row of your current selection. If your selection starts at row 84, update the formula to: =AND(I84<>"", J84="", TODAY()-I84>=3).

Apply and save changes

Click OK to save the formula. Verify that the 'Applies to' box contains the correct range (e.g., =$I$84:$J$100), then click Apply to see the cells turn green.

Align Formula References with the Applied Data Range

Relative vs. Absolute References: Make sure you use relative row references (e.g., I84 instead of I$84) in your formula so the rule correctly adapts and shifts for each subsequent row in your data range.

Solution 2

Verify and Fix Invalid Date Formats

Convert text strings to valid Excel dates so that date math functions like TODAY() can evaluate the elapsed time accurately.

Smart Spreadsheet Solution

Easily Manage Conditional Formatting Rules with WPS Spreadsheet

WPS Office provides a highly intuitive Conditional Formatting manager that makes it easy to set up, troubleshoot, and apply complex date-based color rules. With seamless Excel compatibility, you can open your workbooks and fix rule references effortlessly.

1. Open your workbook: Launch WPS Spreadsheet and open your existing Excel workbook containing the dates.
2. Select the date range: Highlight the specific cells you want to turn green, noting the exact starting row number.
3. Create a new formula rule: Go to Home > Conditional Formatting > New Rule, and select 'Use a formula to determine which cells to format'.
4. Input the relative formula: Enter your date formula matching the first row of your selection (e.g., =TODAY()-I84>=3).
5. Set the green format: Click the Format button, choose a green background fill, and click OK to apply the rule instantly.

100% compatible with Microsoft Excel conditional formatting formulasIntuitive Rules Manager to easily verify 'Applies to' rangesCompletely free, lightweight, and fast spreadsheet softwareSupports all advanced logical and date functions including TODAY() and AND()

microsoft office alternative - wps office

Frequently Asked Questions

Why does my conditional formatting formula highlight the wrong rows?

This usually happens when the cell reference in your formula doesn't match the first row of your 'Applies to' range. If your range starts at row 5 but your formula references row 2, the highlighting will be offset by three rows. Always write the formula relative to the top-left cell of your selection.

How do I conditionally format a cell based on today's date?

You can use the TODAY() function in your conditional formatting formula. For example, to highlight a cell if its date is older than today, select 'Use a formula to determine which cells to format' and use a formula like =A1<TODAY().

Are there other formulas I can use to calculate elapsed days for formatting?

Yes. Aside from standard subtraction like TODAY()-I2>=3, you can use the DAYS function. For example, =DAYS(TODAY(), I2)>=3 will achieve the exact same result by calculating the difference between the two dates.

Excel & Data Problems