VBA & Macro Problems

How to Use Excel Conditional Formatting and VBA Prompts for Service Status

Steven Parker Oct 14, 2026 869 views

Question details

The user needs to assign a Mastering Service status based on Distribution time, color-code it using conditional formatting (RAG), and use a macro to prompt the user for comments when a disruption occurs.

How to Use Excel Conditional Formatting and VBA Prompts for Service Status

Product: Excel
Device & OS: not provided
Scenario: Tracking Mastering Service status using a Red, Amber, Green (RAG) system, requiring mandatory user explanations when a Minor or Major Disruption is calculated.
Observed behavior: The goal state is to have the status automatically calculate and color-code, while a VBA macro detects disruption statuses and opens a mandatory input box to capture and record user commentary.

Before you start

Ensure your Excel workbook is saved as a Macro-Enabled Workbook (.xlsm) and that you have the Developer tab enabled in the ribbon to access the VBA editor.

Solution 1Recommended

Combine Lookup Formulas, Conditional Formatting, and a VBA Worksheet_Change Event

This solution calculates the RAG status using formulas, formats the cells automatically, and uses a VBA event to monitor changes in the Distribution time, prompting for a comment when a disruption is detected.

Because Excel's Worksheet_Change event only triggers on physical user data entry (not formula calculations), the VBA code must monitor the cell where the user manually enters the Distribution time, rather than the formula cell containing the service status.

Set up the status formula

In your Mastering Service status column, use an IF or VLOOKUP formula to evaluate the Distribution time and output 'Good Service', 'Minor Disruption', or 'Major Disruption'.

Apply RAG Conditional Formatting

Highlight the Mastering Service column. Navigate to Home > Conditional Formatting > Highlight Cells Rules > Equal To. Set 'Good Service' to green, 'Minor Disruption' to amber (yellow), and 'Major Disruption' to red.

Open the VBA Editor

Press ALT + F11 to open the Visual Basic for Applications window. In the Project Explorer on the left, double-click the specific worksheet (e.g., Sheet1) where your data resides.

Insert the Worksheet_Change macro

Paste a Private Sub Worksheet_Change(ByVal Target As Range) script. Configure it to check if the changed Target cell is in the Distribution time column. If it is, have the code check the corresponding Service Status cell. If the status equals 'Minor Disruption' or 'Major Disruption', trigger an InputBox and write the user's response to the Commentary column.

Save as a Macro-Enabled Workbook

Go to File > Save As, and choose 'Excel Macro-Enabled Workbook (*.xlsm)' from the file type dropdown menu to ensure your VBA code is preserved.

Combine Lookup Formulas, Conditional Formatting, and a VBA Worksheet_Change Event

Macro Security: Users will need to click 'Enable Content' or 'Enable Macros' when opening the workbook to allow the VBA pop-up script to run.

Advanced Data Management with WPS Office

Set Up Conditional Formatting and Macros in WPS Spreadsheets

WPS Spreadsheets provides comprehensive support for complex formulas, dynamic conditional formatting, and VBA macros, enabling you to automate your RAG status tracking workflows effortlessly.

1. Prepare Your Data: Open your dataset in WPS Spreadsheets and apply VLOOKUP or IF functions to generate your service statuses.
2. Apply Conditional Formatting: Navigate to the Home tab, click Conditional Formatting, and define your highlight rules for Good, Minor, and Major statuses.
3. Add Your VBA Macro: Click the Developer tab, select VBA Editor, and paste your Worksheet_Change code into the relevant sheet module to trigger comment pop-ups.
4. Save as XLSM: Save your document in the .xlsm format to retain both your custom formatting rules and your VBA automation.

Full compatibility with Microsoft Excel formats (.xlsx, .xlsm, and .xlsb).Built-in VBA editor to write, modify, and run event macros seamlessly.Intuitive Conditional Formatting interface for quick visual data analysis.Lightweight software that processes complex macros and large datasets quickly.

microsoft office alternative - wps office

Frequently Asked Questions

Why isn't my VBA macro triggering when the service status formula updates?

The Worksheet_Change event in VBA only detects manual data entry by a user. It does not detect when a formula's output changes. To fix this, set your VBA macro to monitor the input column (Distribution time) rather than the formula output column.

How do I ensure users cannot skip the commentary pop-up?

In your VBA script, you can use a Do...Loop statement around the InputBox. You can configure the loop to keep prompting the user until the InputBox result is not empty, preventing them from leaving the comment blank.

Why does my conditional formatting stop working when I copy and paste data?

Pasting data usually overrides the existing cell formatting. To preserve your conditional formatting rules, use 'Paste Special' and select 'Values' (or press Ctrl + Shift + V) when inserting new distribution times.

Can I use VBA macros in the online version of Excel?

No, VBA macros are only supported in the desktop versions of Excel and WPS Spreadsheets. If you open a macro-enabled workbook in a web browser, the Worksheet_Change event will not trigger the pop-up.

Excel & Data Problems