VBA & Macro Problems

Copy Matching Cell Colors Between Excel Tables with VBA

Anum MushtaqAnum Mushtaq Nov 18, 2026 869 views

Question details

Copy the fill color of a cell in one table to a cell with the same value in another table.

Copy Matching Cell Colors Between Excel Tables with VBA
Product
Microsoft Excel
Device & OS
not provided
Scenario
Two tables contain common values, and the user wants matching cells in the target table to use the source cells' fill colors.
Observed behavior
Matching target cells should receive the fill colors of their corresponding source cells.
Before you start

Identify the target and source columns and their first data row. The example uses column B as the target and column E as the source, with data starting in row 3 on the active worksheet.

Solution 1Recommended

Use an Excel VBA macro to copy matching fill colors

Use this macro when both columns are on the same worksheet and you want to copy manually applied fill colors based on matching values.

The macro clears existing fills in the target data range, finds the first matching source value, and copies its fill color. It does not copy other formatting.

The macro reads the source cell's underlying fill, not a color displayed by conditional formatting. Excel's MATCH function also treats asterisks and question marks in text as wildcards.

1
Open the VBA editor

In Excel, open the workbook and select Developer > Visual Basic. If Developer is hidden, enable it in Excel's ribbon customization settings.

2
Insert the macro

In the VBA editor, select Insert > Module and paste this code: Sub CopyMatchingColors() Dim ws As Worksheet Dim lastTarget As Long, lastSource As Long Dim i As Long, sourceRow As Long Dim matchPos As Variant Set ws = ActiveSheet lastTarget = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row lastSource = ws.Cells(ws.Rows.Count, "E").End(xlUp).Row If lastTarget < 3 Then Exit Sub ws.Range("B3:B" & lastTarget).Interior.ColorIndex = xlColorIndexNone If lastSource < 3 Then Exit Sub For i = 3 To lastTarget If Not IsError(ws.Cells(i, "B").Value) Then If Len(ws.Cells(i, "B").Value) > 0 Then matchPos = Application.Match(ws.Cells(i, "B").Value, ws.Range("E3:E" & lastSource), 0) If Not IsError(matchPos) Then sourceRow = CLng(matchPos) + 2 If ws.Cells(sourceRow, "E").Interior.ColorIndex <> xlColorIndexNone Then ws.Cells(i, "B").Interior.Color = ws.Cells(sourceRow, "E").Interior.Color End If End If End If End If Next i End Sub

3
Adjust the ranges if needed

In the module, replace references to B and E with your target and source columns. If your data starts on a different row, update the row 3 references and the source-row offset.

4
Run and save the macro

Return to the worksheet, select Developer > Macros, choose CopyMatchingColors, and select Run. Use File > Save As and choose Excel Macro-Enabled Workbook (.xlsm) to retain the macro.

Use an Excel VBA macro to copy matching fill colors
Existing target fills are cleared: Unmatched target cells will have no fill after the macro runs. If a source value occurs more than once, the first match supplies the color.
Work with matching colors in WPS

Copy matching cell colors in WPS Spreadsheet

In a WPS Spreadsheet installation that supports VBA macros, use the same matching-color macro to transfer fills between columns. VBA availability may depend on your WPS edition and installed components.

  1. 1. Open the workbook: Open your Excel workbook in WPS Spreadsheet and select the worksheet containing the target and source columns.
  2. 2. Check VBA availability: Look for Developer > Visual Basic. If the VBA editor is unavailable, check whether your WPS edition supports VBA and whether its required component is installed.
  3. 3. Add the macro: In the VBA editor, select Insert > Module and paste the CopyMatchingColors code from the solution above. Adjust the column and starting-row references to match your worksheet.
  4. 4. Run and save: Return to the worksheet, select Developer > Macros, and run CopyMatchingColors. Save as an XLSM workbook if you need to retain the macro.
Copy source fill colors to cells with matching values using a VBA macroOpen and work with Microsoft Excel formats, including XLSX and XLSMUse a familiar spreadsheet interface for reviewing and editing tables
microsoft office alternative - wps office

Frequently Asked Questions

Does the macro copy colors created by conditional formatting?

No. It reads the source cell's underlying Interior fill. To reproduce a conditionally displayed color, use an appropriate conditional formatting rule on the target range or adapt the macro to read the displayed format.

What happens when a value appears more than once in the source column?

Application.Match returns the first match in the source range, so the macro uses that cell's fill color.

Will the macro remove other formatting from the target cells?

No. It clears only the fill in the target data range; it does not clear fonts, borders, or number formats.

Why can't I find the Developer tab in Excel?

Open Excel's ribbon customization settings and enable Developer, then use Developer > Visual Basic or Developer > Macros.