How to Auto-Fill Cleared Cells with 'VACANT' using Excel VBA
Question details
The user needs specific Excel cells to automatically display the word 'VACANT' when existing data is cleared, while applying distinct font formatting for empty versus filled states.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Tracking spreadsheet data where deleted cell entries must clearly show a 'VACANT' placeholder instead of remaining blank.
- Observed behavior
- Empty cells need to be auto-populated with 'VACANT' in red bold text, but when a user enters new replacement text, the cell must automatically return to normal black, non-bold formatting.
Ensure that your workbook is saved as a Macro-Enabled Workbook (.xlsm) and that macros are enabled in your spreadsheet program's security settings.
Use a Worksheet_Change VBA Event
Utilize a built-in Worksheet VBA event to monitor cell edits. When a cell is cleared, the macro will automatically insert 'VACANT' and adjust the font formatting accordingly.
The Worksheet_Change event allows VBA code to run automatically whenever a cell's value is modified by the user. By evaluating the target cell's content, the code can apply formatting dynamically.
It is highly important to temporarily disable application events during this process. Because the macro itself changes the cell's value, failing to disable events will cause the macro to trigger itself repeatedly, leading to an infinite loop.
Press Alt + F11 on your keyboard to open the Visual Basic Editor in your spreadsheet program.
In the Project Explorer pane on the left side, double-click the specific worksheet (e.g., Sheet1) where you want this automated action to occur.
Select 'Worksheet' from the left drop-down menu above the code window, and 'Change' from the right drop-down menu to create the Private Sub Worksheet_Change(ByVal Target As Range) block.
Write an If statement to check if the target cell is empty (If Target.Value = ""). Inside this block, first type Application.EnableEvents = False. Then, set Target.Value = "VACANT", Target.Font.Color = vbRed, and Target.Font.Bold = True.
Create an Else block for when the cell contains data. Set Target.Font.ColorIndex = xlAutomatic (or vbBlack) and Target.Font.Bold = False.
After the End If statement, ensure you add Application.EnableEvents = True before the End Sub line so that future cell edits are detected.
Create and Manage Macros Easily with WPS Spreadsheet
WPS Spreadsheet fully supports VBA macros, allowing you to easily automate tasks like auto-filling cleared cells. Enjoy a seamless coding experience that is highly compatible with Microsoft Excel.
- 1. Download and install WPS Office: Get the latest version of WPS Office and install it on your computer.
- 2. Open your macro workbook: Open your .xlsm file in WPS Spreadsheet.
- 3. Access the Developer tab: Navigate to the Developer tab on the top ribbon and click 'Visual Basic' to launch the VBA Editor.
- 4. Insert the automation code: Double-click your target worksheet in the Project window and paste the Worksheet_Change VBA code to enable auto-filling.
Frequently Asked Questions
How do I restrict the VBA macro to a specific range like A1:A10?
You can restrict the macro by adding a condition using the Intersect method. For example, add 'If Not Intersect(Target, Range("A1:A10")) Is Nothing Then' right after your Worksheet_Change subroutine starts, and close it with 'End If' at the end.
Why does my spreadsheet freeze when the VBA macro runs?
This happens due to an infinite loop. When the macro changes a cell's value to 'VACANT', it triggers the Worksheet_Change event again. To prevent this, always include 'Application.EnableEvents = False' before modifying cells in the code, and set it back to 'True' when done.
How do I return the cell font color back to automatic or black for manually entered data?
In the 'Else' branch of your VBA code (which handles states where the cell is not blank), set 'Target.Font.ColorIndex = xlAutomatic' or 'Target.Font.Color = vbBlack', and set 'Target.Font.Bold = False'. This ensures any manually typed text displays normally.
