Macro to Format a Spill Range in Excel

VBA and spill ranges can work together

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/

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

2 thoughts on “Macro to Format a Spill Range in Excel

  1. 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.