Formatting Issues

How to Apply Excel Conditional Formatting Based on a Date in Another Cell

Maira Mehtab Sep 22, 2026 869 views

Question details

The user wants to automatically change the fill color of cells in one column (e.g., Column A) when the date in a different column (e.g., Column AQ) is equal to today or a future date.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Setting up an automated tracking sheet where target cells highlight dynamically based on a date condition in a reference column, such as upcoming deadlines.
Observed behavior: When the formatting is properly configured, cells in the target range dynamically update their background color based on the current system date without manual intervention.

Before you start

Ensure that the cells in your reference date column are formatted as actual 'Date' values rather than text; otherwise, the conditional formatting formula will not be able to evaluate them properly against the TODAY() function.

Solution 1Recommended

Use a Formula-Based Conditional Formatting Rule

Create a custom conditional formatting rule using the TODAY() function to evaluate dates in the reference column.

To format one column based on the value in another, you must use a formula-based rule rather than standard preset rules. By using the formula =$AQ2>=TODAY(), Excel compares the date in column AQ to the current system date. The absolute column reference ($) ensures the condition is strictly tied to column AQ, while the relative row reference (2) allows the rule to cascade correctly down your selection.

Select the target range

Click and drag to highlight the cells you want to change color, such as A2:A100. Ensure that the active cell (the one displaying in the top-left Name Box) is the first cell of your selection, like A2.

Open Conditional Formatting

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

Enter the custom formula

In the dialog box, choose 'Use a formula to determine which cells to format'. In the formula input box, type the exact formula: =$AQ2>=TODAY()

Set the fill color and save

Click the 'Format' button, switch to the 'Fill' tab, and select the background color you want to apply. Click 'OK' to close the Format Cells dialog, then click 'OK' again to apply the conditional formatting rule.

Understanding Cell Referencing: The dollar sign ($) before the column letter (AQ) is essential. It locks the condition to always check column AQ, regardless of which column the formatting is actually applied to.

WPS Spreadsheet Solution

Easily Apply Conditional Formatting with WPS Spreadsheet

WPS Spreadsheet seamlessly handles complex logical tests and custom date formulas. You can manage large datasets, apply cross-column conditional formatting, and track dynamic dates effortlessly.

1. Select the cells to format: Open your workbook in WPS Spreadsheet and highlight the target range (e.g., A2:A100) where you want the background color to appear.
2. Access Conditional Formatting: Navigate to the Home tab, click on 'Conditional Formatting', and choose 'New Rule' from the list.
3. Input the date formula: Select the option to use a formula, input =$AQ2>=TODAY() into the formula field, set your preferred format color, and click 'OK'.

Fully compatible with Microsoft Excel conditional formatting formulas and rules.Supports advanced date functions like TODAY() and complex absolute/relative cell referencing.Free to use, lightweight, and works seamlessly across Windows, Mac, and mobile platforms.

Frequently Asked Questions

Why is my conditional formatting applying to the wrong rows?

This usually happens if your active cell when making the initial selection does not match the row number in your formula. If you highlighted A2:A100, ensure your formula explicitly references row 2 (e.g., =$AQ2). If the formula references row 1, the formatting will be offset by one row.

How do I highlight the entire row instead of just one cell?

Instead of selecting just column A, select your entire data range (for example, A2:Z100). Use the exact same formula (=$AQ2>=TODAY()). The absolute column reference ($AQ) will force Excel to evaluate the date in column AQ and apply the fill color across the entire selected row.

Can I use a specific date instead of today's date?

Yes. Instead of the TODAY() function, you can use the DATE function. For example, to format cells based on January 1, 2024, you would change your formula to: =$AQ2>=DATE(2024,1,1).

Will this formatting update automatically tomorrow?

Yes. The TODAY() function is volatile, meaning it automatically updates to the current system date every time you open the workbook or whenever the sheet recalculates.

Document & File Issues