Copy Excel Cell Fill Colors Between Workbooks with VBA
Question details
Copy the fill color associated with each team name from a master workbook to matching cells in other workbooks.

- 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.
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.
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.
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.
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
Save the master as an Excel Macro-Enabled Workbook (.xlsm). With the destination workbooks open, select Developer > Macros, choose CopyTeamFillColors, and click Run.
Inspect matching cells in the destination workbooks, then use File > Save in each workbook to keep the updated fills.

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.

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.




