How to Auto-Fill an Excel Cell While Preserving Formatting using VBA
Question details
The user needs a way to pull data from a source table into a destination cell or text box while keeping the original text's rich formatting.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Looking up and returning matching data from another sheet based on a criteria, similar to what VLOOKUP does, but requiring the source cell's styling to transfer as well.
- Observed behavior
- Standard formulas like VLOOKUP only return the plain text value and strip all rich text formatting, including bold, italics, fonts, and colors.
Before running any VBA scripts, ensure you save your workbook as a Macro-Enabled Workbook (.xlsm) and back up your data to prevent accidental overwrites.
Use a VBA Macro to Copy and Paste Formatting
Replace standard lookup formulas with a VBA macro that uses the Copy method to physically transfer the source cell, bringing both its content and its styling to the new location.
Standard Excel formulas evaluate data and are fundamentally designed to return values only, completely omitting the original cell's rich formatting layer. To transfer attributes like bolding, italics, fonts, and colors, you must bypass formulas and use a VBA script that locates the match and performs a true copy-paste action.
Press Alt + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor.
In the top menu, click 'Insert' and select 'Module' to create a blank workspace for your code.
Copy and paste the following snippet into the module window: Sub CopyWithFormatting() Dim r As Range Dim v As Variant v = Application.Match(Worksheets("Sheet1").Range("A2").Value, Worksheets("Sheet2").Range("A:A"), False) If Not IsError(v) Then Set r = Worksheets("Sheet2").Range("B:B").Cells(v) r.Copy Worksheets("Sheet1").Range("B2") End If End Sub
Close the VBA editor. From your worksheet, press Alt + F8, select 'CopyWithFormatting', and click 'Run'. The destination cell will update with both the value and the exact formatting from the source cell.
Easily Manage VBA Macros and Retain Formatting with WPS Spreadsheet
WPS Office provides robust built-in support for VBA and Macros, allowing you to seamlessly run customized scripts that copy both data and rich formatting. It offers an easy-to-use development environment fully equipped to handle advanced data lookup automations.
- 1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open your .xlsm file containing the data and target destination cells.
- 2. Access the Developer Tools: Navigate to the 'Developer' tab on the top ribbon. If it is hidden, you can enable it through the WPS Options menu under customize ribbon.
- 3. Open the VBA Editor: Click the 'VB Editor' icon in the Developer tab or press Alt + F11 to launch the macro scripting environment.
- 4. Execute Your Lookup Macro: Paste your Application.Match and Copy script into a new module and execute it to perfectly transfer your customized rich text formatting.
Frequently Asked Questions
Why doesn't VLOOKUP copy cell colors or bold text automatically?
Excel lookup formulas, including VLOOKUP, INDEX/MATCH, and XLOOKUP, are designed strictly to evaluate references and return data values. They do not interact with the styling, metadata, or formatting layers of a cell.
Can I link a text box to a cell and keep its original formatting without macros?
If you link a standard text box to a cell by typing '=' and clicking the cell, it will only display plain text. To keep formatting visually without macros, you can use the 'Linked Picture' feature (Camera tool) to create a live, stylized image of the source cell.
Do I need to save my workbook differently after adding VBA code?
Yes. Once you incorporate a macro into your workbook, you must use 'Save As' and select the 'Excel Macro-Enabled Workbook (*.xlsm)' format. Saving as a standard .xlsx file will permanently delete your macro code.
How do I trigger this formatting macro automatically when a cell value changes?
You can automate the execution by placing your VBA code inside the 'Worksheet_Change' event on the specific sheet's module. Use 'If Not Intersect(Target, Range("A2")) Is Nothing Then' to run the copy script immediately whenever the lookup value is updated.
