Currently spill ranges do not spill formats. Hopefully Microsoft will add this functionality soon. In the meantime, here is a macro that will copy the format from the top left cell of the spill range to the rest of the spill range.
You can add this macro to an icon on your Quick Access Toolbar to make it easier to use. There is blog post link at the bottom of this post to assist if you are new to macros.
The macro works if you have a cell selected within the spill range. If you have a cell outside a spill range or a chart or textbox or something else selected, then a message box displays explaining the requirements.
Sub Spill_Format() 'this macro applies the format from the top left cell ' of a spill range to the whole spill range Dim rng As Range Dim bError As Boolean If TypeName(Selection) = "Range" Then 'check a range is selected Set rng = Selection 'capture the selection If rng.HasSpill = True Then 'if it is a spill range rng.SpillParent.Copy 'copy the top left cell 'paste the format to the whole spill range rng.SpillParent.SpillingToRange.PasteSpecial Paste:=xlPasteFormats Application.CutCopyMode = False 'clear the clipboard rng.Select 'reselect the selection cell Else bError = True 'capture error End If Else bError = True 'capture error End If 'display an error message if an error is encountered If bError Then MsgBox "Please select a cell within a spill range before using this macro." _ , vbOKOnly + vbExclamation, "Selection error" End If Set rng = Nothing 'clear the variable End Sub |
The macro checks if the selection is a range. If it is, then it checks if the cell is part of a spill range. If it is, it copies the top left cell and pastes the format to the whole spill range.
If your spill range expands you will need to re-run the macro.
If you are unsure how to use macros check out the blog post below.
https://a4accounting.com.au/how-to-make-the-most-of-excel-macros/
Here is another way to write your macro…
Sub Spill_Format()
If ActiveCell.HasSpill Then
With ActiveCell.SpillParent
.SpillingToRange.NumberFormat = .NumberFormat
End With
Else
MsgBox “Please select a cell within a spill range before using this macro.”, _
vbOKOnly + vbExclamation, “Selection error”
End If
End Sub
Thanks Rick.
I always forget about ActiveCell.
I would add the copy and paste as I want all the formats, not just the number formats.