VBA & Macro Problems

Copy Excel Cell Fill Colors Between Workbooks with VBA

Ayan MasoodAyan Masood Nov 20, 2026 869 views

Question details

Copy the fill color associated with each team name from a master workbook to matching cells in other workbooks.

Copy Excel Cell Fill Colors Between Workbooks with VBA
Product
Microsoft Excel
Device & OS
not provided
Scenario
Destination workbooks reference team data in a master workbook, and the user wants matching cells to use the master list's fill colors.
Observed behavior
Formulas and workbook links transfer values but do not automatically transfer cell formatting.
Before you start

Open the master workbook and the destination workbooks in the same Excel session. Confirm that destination sheets can be edited and that the master list contains one team name per cell with its intended fill color.

Solution 1Recommended

Use VBA to copy fill colors to matching team names

Run a macro in the master workbook to update matching cells in all other open workbooks without changing their other formatting.

This example reads team names and manually applied fills from column A of a sheet named Teams, starting in row 2. It matches whole cell values and updates every match on every worksheet in the other open workbooks.

1
Set up the master list

In the master workbook, name the source worksheet Teams. Put team names in column A starting at A2, and apply the desired fill to each name.

2
Add the macro

Open the Visual Basic Editor from Developer > Visual Basic, choose Insert > Module, and paste the following code: Sub CopyTeamFillColors() Dim sourceSheet As Worksheet, sourceCell As Range Dim wb As Workbook, ws As Worksheet Dim hit As Range, firstAddress As String Dim lastRow As Long, teamName As String, searchText As String Set sourceSheet = ThisWorkbook.Worksheets("Teams") lastRow = sourceSheet.Cells(sourceSheet.Rows.Count, "A").End(xlUp).Row For Each sourceCell In sourceSheet.Range("A2:A" & Application.Max(2, lastRow)) If Not IsError(sourceCell.Value) Then teamName = CStr(sourceCell.Value) If Len(teamName) > 0 Then searchText = Replace(teamName, "~", "~~") searchText = Replace(searchText, "*", "~*") searchText = Replace(searchText, "?", "~?") For Each wb In Application.Workbooks If Not wb Is ThisWorkbook Then For Each ws In wb.Worksheets Set hit = ws.Cells.Find(What:=searchText, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False) If Not hit Is Nothing Then firstAddress = hit.Address Do If sourceCell.Interior.ColorIndex = xlColorIndexNone Then hit.Interior.ColorIndex = xlColorIndexNone Else hit.Interior.Color = sourceCell.Interior.Color End If Set hit = ws.Cells.FindNext(hit) If hit Is Nothing Then Exit Do Loop While hit.Address <> firstAddress End If Next ws End If Next wb End If End If Next sourceCell End Sub

3
Save and run the macro

Save the master as an Excel Macro-Enabled Workbook (.xlsm). With the destination workbooks open, select Developer > Macros, choose CopyTeamFillColors, and click Run.

4
Check and save the results

Inspect matching cells in the destination workbooks, then use File > Save in each workbook to keep the updated fills.

Use VBA to copy fill colors to matching team names
Fill color limitations: The macro copies the cell's direct fill, not a color displayed by conditional formatting. Protected sheets or read-only files can prevent changes or saving. For automatic updates, call the macro from an appropriate workbook or worksheet event, ensuring the destination workbooks are already open.
Free Microsoft Office alternative

Work with Excel spreadsheets in WPS Office

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface. Open compatible Excel files and use cell fill tools for everyday formatting as you move between office suites. Excel VBA support varies by WPS version, so do not assume this macro will run unchanged.

Apply and adjust cell fill colors in WPS SpreadsheetOpen and edit common Microsoft Excel formats such as .xlsxUse a familiar ribbon-style interfaceWork with a free, lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

Why don't Excel links copy cell colors?

A formula or workbook link returns a value, not the source cell's formatting. Use VBA or apply formatting separately to give the destination cell a fill.

Can the macro update workbooks that are closed?

No. Open each destination workbook in the same Excel session before running Developer > Macros > CopyTeamFillColors.

Does the macro copy conditional formatting colors?

No. It reads the source cell's Interior property, which represents its direct fill. Conditional formatting rules and their displayed colors are not copied.

Can I run the color update automatically?

Yes. You can call CopyTeamFillColors from an event such as Workbook_Open or Worksheet_Change. The destination workbooks must be open when the macro runs, and Worksheet_Change does not fire merely because a formula recalculates.

Will the macro change fonts or borders?

No. It changes only the destination cell's fill color, or removes the fill when the source cell has no fill.