Formatting Issues

WPS Spreadsheet: Apply Red and Green Conditional Formatting to Deadline Dates

Natalie Taylor Nov 12, 2026 869 views

Question details

The user needs to color selected dates according to whether they meet a due date stored in another cell.

Product: WPS Spreadsheet
Device & OS: not provided
Scenario: A worksheet contains selection dates in I4:I10 and a due date in H4. The user wants dates on or before the due date shown in green and later dates shown in red.
Observed behavior: The date cells need formula-based conditional formatting that compares each date with the fixed due date reference in H4.

Before you start

Confirm that H4 contains a valid date and that the dates in I4:I10 are stored as date values rather than text. Select I4:I10 with I4 as the active cell before creating the formula rules.

Solution 1Recommended

Create formula-based green and red deadline rules in WPS Spreadsheet

Use this solution when dates in one range must be compared with a fixed due date in another cell.

The first cell reference, I4, stays relative so each row is evaluated against its own date. The due-date reference, $H$4, stays absolute so every row compares against the same deadline.

Select the deadline range

In WPS Spreadsheet, select cells I4:I10 and make sure I4 is the active cell.

Open a new conditional formatting rule

Go to Home > Conditional Formatting > New Rule, then choose the option to use a formula to determine which cells are formatted.

Add the green on-time rule

Enter the formula =AND(I4<>"",I4<=$H$4), choose a green fill color, and confirm the rule.

Add the red overdue rule

Create another formula rule from Home > Conditional Formatting > New Rule, enter =I4>$H$4, choose a red fill color, and apply it to I4:I10.

Check the results

Verify that dates on or before the date in H4 are green and dates after H4 are red. Edit H4 to confirm that the colors update automatically.

Keep the due-date reference absolute: Use $H$4 in both rules so the comparison always points to the due date cell, while I4 remains relative for row-by-row evaluation.

Manage deadline status with WPS Spreadsheet conditional formatting

WPS Spreadsheet lets you compare date cells with a fixed deadline and automatically apply green or red fills using formula-based conditional formatting rules.

1. Open WPS Spreadsheet: Launch WPS Office and open the workbook containing the deadline dates.
2. Select the date cells: Select I4:I10 and keep I4 active for the relative formula reference.
3. Create the green rule: Choose Home > Conditional Formatting > New Rule, enter =AND(I4<>"",I4<=$H$4), and apply a green fill.
4. Create the red rule: Add another formula rule with =I4>$H$4 and apply a red fill to the same range.

Automatically highlights on-time and overdue datesSupports formula-based comparisons with absolute cell referencesWorks with Microsoft Excel XLSX spreadsheetsOffers a familiar spreadsheet interface for quick formatting changes

microsoft office alternative - wps office

Frequently Asked Questions

Why must the due-date reference be written as $H$4?

The dollar signs make H4 an absolute reference, so every cell in I4:I10 compares its date with the same due date instead of shifting the reference to H5, H6, and so on.

What formula makes dates on or before the deadline green?

For the range beginning at I4, use =AND(I4<>"",I4<=$H$4). The first condition prevents blank cells from being formatted, and the second checks whether the date meets the deadline.

How do I mark dates after the due date in red?

Create a second rule through Home > Conditional Formatting > New Rule and use =I4>$H$4, then select a red fill color.

Why are the conditional formatting colors not changing after I edit H4?

Check that H4 and the cells in I4:I10 contain actual date values, not text. Also open Home > Conditional Formatting > Manage Rules and confirm both rules apply to I4:I10.

Document & File Issues