How to Copy a Matching Excel Cell Value and Fill Color with VBA in WPS Office
Question details
The user needs a VBA macro that matches a manually entered value in one column against a reference list, then copies the related text value and interior fill color to a destination column.
- Product
- WPS Office Excel
- Device & OS
- not provided
- Scenario
- A spreadsheet has a reference range with values and colored cells in columns A:B, and the user manually enters lookup values in column D.
- Observed behavior
- Standard worksheet formulas such as VLOOKUP return only the cell value and cannot copy the source cell's fill color, so VBA is required.
Save the workbook as a macro-enabled .xlsm file and confirm that the Developer tab is visible in WPS Office Excel so you can open the Visual Basic editor and run VBA macros.
Run a VBA Macro That Matches Values and Copies Text and Fill Color
Use this solution when you need to copy both the matched value and the interior fill color from a reference row. It clears column E, matches column D values against column A, and writes the corresponding column B value and ColorIndex into column E.
Standard lookup formulas such as VLOOKUP or INDEX/MATCH return values, but they do not expose a cell's Interior.Color or Interior.ColorIndex. A short VBA routine performs the lookup and copies the formatting in one pass.
The macro below restricts the lookup range to the used data area instead of scanning thousands of unused rows, which improves performance on large workbooks.
In WPS Office Excel, click the Developer tab and select Visual Basic, or press Alt+F11 to open the VBA editor.
In the Project Explorer, right-click VBAProject, choose Insert > Module, and open the new module window.
Paste the following macro into the module: Sub Colors() Dim i As Long, j As Long, k As Variant Range("E:E").Clear i = Range("D" & Rows.Count).End(xlUp).Row For j = 1 To i k = Application.Match(Cells(j, 4), Range(Cells(1, 1), Cells(i, 1)), 0) If Not IsError(k) Then Cells(j, 5).Value = Cells(k, 2).Value Cells(j, 5).Interior.ColorIndex = Cells(k, 2).Interior.ColorIndex End If Next j End Sub
Click Run Sub/UserForm or press F5 while the cursor is inside the macro. The matching values and fill colors appear in column E for every matched row in column D.
Use Interior.Color Instead of ColorIndex to Copy the Exact RGB Fill Color
Use this variation when the source cells use custom RGB fills or theme colors and you need an exact color match instead of a palette-based match.
Copy Matching Excel Values and Fill Colors with a VBA Macro in WPS Office
WPS Office Excel supports VBA macros, so you can match a manually entered value against a reference list, copy the related text, and apply the source cell color to the destination cell without switching to another spreadsheet application.
- 1. Open WPS Office Excel: Launch WPS Office Excel and open the workbook that contains the reference list and the manually entered values.
- 2. Enable the Developer tab: Right-click the ribbon, choose Customize Ribbon, and enable the Developer tab if it is not already visible.
- 3. Open the VBA editor and insert a module: Click Developer > Visual Basic, insert a new module, and paste the VBA macro that performs the match and copies the fill color.
- 4. Run the macro and verify the result: Run the macro with F5, then check that column E contains the matched text and the same fill color as the corresponding source cell in column B.
Frequently Asked Questions
Why can't I copy fill color with VLOOKUP or INDEX/MATCH?
Worksheet lookup formulas return values, but they cannot read or copy cell formatting such as Interior.Color or Interior.ColorIndex. You need a VBA macro that explicitly assigns the source cell's fill color to the destination cell.
How do I limit the macro to the actual used data range?
Find the last used row with Range("D" & Rows.Count).End(xlUp).Row and use that row number in the Match range, for example Range(Cells(1, 1), Cells(i, 1)), instead of referencing a fixed range of thousands of rows.
How do I copy the exact RGB fill color instead of the palette color index?
Replace .Interior.ColorIndex with .Interior.Color on both the destination and source sides, for example Cells(j, 5).Interior.Color = Cells(k, 2).Interior.Color.
Do I need to save the workbook as macro-enabled?
Yes. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) or a format that supports macros. If you save as .xlsx, the VBA macro will not run.
Can I run this VBA macro in WPS Office Excel?
Yes. WPS Office Excel supports VBA through the Developer tab and Visual Basic editor, and it can run standard Excel-compatible macros for this type of matching and formatting task.
