Formatting Issues

How to Change an Excel Row Color When an Invoice Is Paid

Chanuka Geekiyanage Oct 8, 2026 869 views

Question details

The user wants to automatically highlight rows across specific columns when a payment date is entered into a designated column.

How to Change an Excel Row Color When an Invoice Is Paid

Product: Excel
Device & OS: not provided
Scenario: Managing an invoice tracker and needing a visual indicator to show which invoices have been paid based on the entry of a payment date.
Observed behavior: The target cells in the row should dynamically change their fill color when a valid date is detected in the corresponding payment date cell.

Before you start

Ensure that the payment dates entered in your tracking column are formatted as actual Excel dates rather than plain text, as the conditional formatting formula evaluates numerical date values.

Solution 1Recommended

Use Conditional Formatting with a Custom Formula

Apply a formula-based conditional formatting rule to highlight the entire row when a payment date is entered.

By using a custom logical formula, you can format multiple cells in a row based on the value of a single cell. The absolute column reference ($H) keeps the rule tied to the payment date column, while the relative row reference (2) allows Excel to evaluate each row individually down the sheet.

Select the Target Range

Click and drag to select the cell range you want to highlight, such as A2:H400. It is crucial to start your selection from the top row of your data (row 2 in this case).

Open Conditional Formatting

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

Enter the Formatting Formula

Choose the option 'Use a formula to determine which cells to format'. In the formula box, enter =AND($H2<>"",$H2<=TODAY()) to check that the cell is not empty and contains a date on or before today.

Apply Fill Color

Click the 'Format' button, switch to the 'Fill' tab, and choose your desired highlight color (like yellow or green). Click 'OK' twice to apply the rule to your invoice tracker.

Use Conditional Formatting with a Custom Formula

Clear Conflicting Rules: If the row colors do not change as expected, check for and remove any conflicting conditional formatting rules in your selected range from the 'Manage Rules' menu.

Efficient Invoice Tracking

Track Paid Invoices Easily with WPS Spreadsheet

WPS Spreadsheet provides powerful and intuitive conditional formatting tools to help you manage invoice trackers. You can effortlessly set up automated row highlighting to track payments and manage financial data.

1. Select Your Data: Open your invoice spreadsheet in WPS Office and highlight the data range (e.g., A2:H400).
2. Create a New Rule: Go to the 'Home' tab, click 'Conditional Formatting', and select 'New Rule' from the list.
3. Apply Formula and Format: Select 'Use a formula to determine which cells to format', input your formula such as =AND($H2<>"",$H2<=TODAY()), select your fill color under 'Format', and click 'OK'.

Fully compatible with Microsoft Excel conditional formatting rules and custom formulasFree and lightweight alternative for daily financial tracking and data managementIntuitive interface makes handling large invoice datasets straightforward and fast

microsoft office alternative - wps office

Frequently Asked Questions

Why is my conditional formatting rule highlighting the wrong rows?

This usually happens if the row number in your formula does not match the first row of your selected range. For example, if you select A2:H400 but your formula says $H1, the highlighting will be offset by one row. Always ensure the formula references the top-left cell of your selection.

Can I highlight the row if any text is entered instead of a specific date?

Yes. If you simply want to change the row color when any data is entered into column H (regardless of whether it's a date or text), you can simplify your conditional formatting formula to =$H2<>"".

How do I remove the formatting if I accidentally marked an invoice as paid?

Because the row color is driven by conditional formatting, simply deleting the date entry in column H will automatically remove the highlight color and return the row to its default appearance.

Document & File Issues