VBA & Macro Problems

Automatically Add Due Dates and Conditional Formatting in WPS Spreadsheets

Adam Davis Nov 13, 2026 869 views

Question details

The user needs a project-tracking worksheet to calculate due dates automatically 14 days after a project start date, highlight overdue dates in red, and keep the calculated due-date cells locked and protected.

Automatically Add Due Dates and Conditional Formatting in WPS Spreadsheets

Product: WPS Spreadsheets
Device & OS: not provided
Scenario: The user maintains a project-tracking worksheet and wants due dates to be created automatically from start dates, with visual overdue alerts and protected calculated cells.
Observed behavior: Due dates are not automatically calculated, overdue dates are not highlighted, and the calculated due-date cells are not locked or protected after data entry.

Before you start

Open your workbook in WPS Spreadsheets and identify the columns that contain project start dates and due dates. If you plan to use the VBA method, make sure the Developer tab is visible and that macros are enabled for this workbook.

Solution 1Recommended

Use a worksheet VBA macro to auto-calculate due dates and protect them

Use this method when you want WPS Spreadsheets to calculate the due date immediately after a start date is entered and to clear the result when the start date is not a valid date.

This worksheet-level VBA procedure runs automatically when cells in the start-date range change. It adds 14 days to valid dates and clears the adjacent due-date cell when the entry is not a date.

Open the worksheet VBA editor

Right-click the sheet tab that contains the project tracker, then choose View Code. This opens the VBA editor for that worksheet.

Add the Worksheet_Change macro

Paste the following into the code window: Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Set rng = Intersect(Target, Me.Range("A2:A1000")) If rng Is Nothing Then Exit Sub Application.EnableEvents = False Dim cell As Range For Each cell In rng If IsDate(cell.Value) Then cell.Offset(0, 1).Value = cell.Value + 14 Else cell.Offset(0, 1).ClearContents End If Next cell Application.EnableEvents = True End Sub

Apply conditional formatting to the due-date range

Select the due-date cells, for example B2:B1000, go to Home > Conditional Formatting > New Rule > Use a formula, enter =$B2<TODAY(), click Format, and choose a red fill.

Lock and protect the worksheet

Select the start-date cells, such as A2:A1000, right-click and choose Format Cells > Protection, clear Locked, and keep the due-date cells locked. Then go to Review > Protect Sheet and set a password if needed.

Save the workbook as macro-enabled

Click File > Save As, choose Excel Macro-Enabled Workbook (*.xlsm), save the file, and enable macros when you reopen it.

Use a worksheet VBA macro to auto-calculate due dates and protect them

Due dates now update automatically: After setup, entering a date in column A creates a due date 14 days later in column B, and overdue dates appear in red while the due-date cells remain locked.

Solution 2

Use formulas and a duration column for variable project deadlines

Use this method if you prefer a non-macro workbook or if projects have different completion periods instead of a fixed 14-day duration.

WPS Spreadsheets automation

Add automatic due dates and overdue highlighting in WPS Spreadsheets

WPS Spreadsheets can calculate due dates from start dates with formulas or a worksheet VBA macro, highlight overdue dates in red with conditional formatting, and protect the calculated cells. The workbook remains compatible with Microsoft Excel .xlsm files.

1. Open the VBA editor for the worksheet: In WPS Spreadsheets, right-click the sheet tab and choose View Code to open the worksheet code window.
2. Insert the automatic due-date macro: Paste a Worksheet_Change procedure that checks column A, sets column B to A plus 14 days for valid dates, and clears column B for non-date entries.
3. Create the overdue formatting rule: Select the due-date range, click Home > Conditional Formatting > New Rule > Use a formula, enter =$B2<TODAY(), and set a red fill.
4. Protect calculated cells and save: Unlock the start-date cells, keep the due-date cells locked, click Review > Protect Sheet, then save the workbook as Excel Macro-Enabled Workbook (*.xlsm).

Automatically calculates due dates when a start date is enteredHighlights overdue dates in red without manual formattingLocks formula or macro-calculated due-date cells while allowing start-date editsWorks with Excel .xlsm workbooks for macro-enabled projectsFree and lightweight alternative to Microsoft Excel

microsoft office alternative - wps office

Frequently Asked Questions

Can I calculate due dates in WPS Spreadsheets without using VBA macros?

Yes. In the due-date cell, use a formula such as =IF(A2="","",A2+14) for a fixed 14-day due date. For variable durations, add a duration column and use =IF(A2="","",A2+C2).

How do I make overdue due dates turn red automatically?

Select the due-date range, go to Home > Conditional Formatting > New Rule > Use a formula, enter =$B2<TODAY(), click Format, and choose a red fill.

Why is my macro not running after I reopen the WPS workbook?

Save the file as an Excel Macro-Enabled Workbook (*.xlsm) and make sure macros are enabled in the security settings or trust center when you reopen it.

How do I keep due-date cells locked but still edit start dates?

Select the start-date cells, right-click and choose Format Cells > Protection, clear Locked, and keep the calculated due-date cells locked. Then protect the worksheet from Review > Protect Sheet.

Can I use different project durations instead of always adding 14 days?

Yes. Add a duration column, enter the number of days for each project, and calculate the due date with =IF(A2="","",A2+C2). Apply conditional formatting to the resulting due-date range.

Excel & Data Problems