Automatically Synchronize Excel Cell Formatting Between Sheets
Question details
Keep the formatting on class worksheets consistent with a master schedule.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Class sheets reference five columns of a master schedule across rows 2 through 93.
- Observed behavior
- Formulas transfer cell values but do not transfer borders, fills, fonts, bold, or italics.
Identify the master and class sheet names and the source and destination ranges. The VBA example requires desktop Excel and assumes the same five columns occupy the same cell addresses on every sheet.
Copy master formatting with an Excel VBA macro
Use a macro to refresh formatting on multiple class sheets without replacing their values or formulas.
The example copies formats from A2:E93 on a sheet named Master to the same range on nine class sheets. Change the sheet names and ranges to match your workbook. If the source columns are nonadjacent or map to different destination columns, copy each matching range separately.
Excel does not provide a worksheet event that fires when formatting alone changes. The workbook-open event below refreshes formats when the file opens; run the macro manually after formatting changes during a session.
In desktop Excel, select File > Save As and choose Excel Macro-Enabled Workbook (*.xlsm). Test the macro on a copy first.
Press Alt+F11 to open the VBA editor, select Insert > Module, and paste this code: Sub SyncClassFormats() Dim sheetName As Variant Dim classSheets As Variant classSheets = Array("Class1", "Class2", "Class3", "Class4", "Class5", "Class6", "Class7", "Class8", "Class9") For Each sheetName In classSheets ThisWorkbook.Worksheets("Master").Range("A2:E93").Copy ThisWorkbook.Worksheets(CStr(sheetName)).Range("A2:E93").PasteSpecial Paste:=xlPasteFormats Next sheetName Application.CutCopyMode = False End Sub Replace Master and Class1 through Class9 with the exact worksheet names and adjust both ranges as needed.
In the VBA editor's Project pane, double-click ThisWorkbook and add: Private Sub Workbook_Open() SyncClassFormats End Sub If a Workbook_Open procedure already exists, add SyncClassFormats inside it instead of creating a second one.
Save the workbook, reopen it, and enable macros if prompted and you trust the file. To refresh after a later formatting change, select Developer > Macros, choose SyncClassFormats, and select Run. Check that the class sheet formulas remain intact.

Use conditional formatting for value-based appearance
Use this approach when the desired colors, fonts, or borders can be expressed as rules based on cell values.
Edit class schedules with WPS Spreadsheets
WPS Office is a free, lightweight, easy-to-use alternative with a familiar spreadsheet interface. Open existing Excel workbooks to work with schedules and formatting; check macro compatibility before migrating VBA-dependent files.

Frequently Asked Questions
Why don't Excel formulas copy formatting?
Formulas return values, not the source cell's formatting. Use Home > Conditional Formatting for value-based rules or a VBA macro to copy formats.
Will the macro overwrite my class sheet formulas?
The example uses PasteSpecial with xlPasteFormats, so it copies formatting rather than values or formulas. Check the source and destination ranges before running it.
Does changing a master cell's fill automatically run the macro?
No. Excel does not fire a worksheet change event for a formatting-only edit. Run SyncClassFormats from Developer > Macros after such changes, or reopen the workbook to run the workbook-open event.
Can I use a picture to keep the class sheets synchronized?
An ordinary pasted picture is not a reliable way to synchronize editable cell formatting. Use the format-copying macro or conditional formatting instead.
Can I run this VBA macro in Excel for the web?
Excel for the web cannot run VBA macros. Open the macro-enabled workbook in desktop Excel to run or edit the code.




