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/

Related Posts

Posted on
Categories: Dynamic Arrays, Excel Blog, 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.

11 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

    Reply

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

      Reply

  2. Hi Neal,

    I liked your idea and extended it further. If I have a spilled range, I can automatically select the entire spill range without depending on the format of the first cell.

    Sub FormatSpilledRangeWithInputBox()
    Dim spilledRange As Range
    Dim formatType As String
    Dim numberFormat As String
    Dim formatOptions As String

    ‘ Define the available formats
    formatOptions = “Please choose a format:” & vbCrLf & _
    “1. General” & vbCrLf & _
    “2. Number” & vbCrLf & _
    “3. Currency” & vbCrLf & _
    “4. Accounting” & vbCrLf & _
    “5. Date” & vbCrLf & _
    “6. Text”

    ‘ Check if the active cell has a spilled range
    If Not ActiveCell.HasSpill Then
    MsgBox “The active cell does not have a spilled range.”
    Exit Sub
    End If

    ‘ Get the spilled range
    Set spilledRange = ActiveCell.SpillingToRange

    ‘ Get the selected format type from the user
    formatType = InputBox(formatOptions, “Select Format”)

    ‘ Determine the appropriate number format based on user input
    Select Case formatType
    Case “1”, “General”, “general”
    numberFormat = “Value”
    Case “2”, “Number”, “number”
    numberFormat = “#,##0.00_);[Red](#,##0.00);-_)”
    Case “3”, “Currency”, “currency”
    numberFormat = “$#,##0.00_);[Red]($#,##0.00);-_)”
    Case “4”, “Accounting”, “accounting”
    numberFormat = “$#,##0_);[Red]($#,##0);-_)”
    Case “5”, “Date”, “date”
    numberFormat = “dd/mm/yyyy”
    Case “6”, “Text”, “text”
    numberFormat = “@”
    Case Else
    MsgBox “Invalid selection. Defaulting to General.”
    numberFormat = “General”
    End Select

    ‘ Apply the selected format to the spilled range
    spilledRange.numberFormat = numberFormat

    MsgBox “Formatting applied to the spilled range.”
    End Sub

    Kind Regards,

    Bhavik

    Reply

  3. Hi Neal,

    Using a table to populate an Excel VBA InputBox would be ideal. Table Name CaseTable
    Case Format Type Value
    1 General Value
    2 Number #,##0.00_);[Red](#,##0.00);-_)
    3 Currency $#,##0.00_);[Red]($#,##0.00);-_)
    4 Accounting $#,##0_);[Red]($#,##0);-_)
    5 Date dd/mm/yyyy
    6 Text @

    Sub FormatSpilledRangeWithInputBoxTable()
    Dim spilledRange As Range
    Dim formatType As String
    Dim numberFormat As String
    Dim formatOptions As String
    Dim caseTable As ListObject
    Dim caseRow As ListRow
    Dim caseDict As Object

    ‘ Create a dictionary to hold the case values
    Set caseDict = CreateObject(“Scripting.Dictionary”)

    ‘ Set reference to the table named “CaseTable”
    Set caseTable = ActiveSheet.ListObjects(“CaseTable”)

    ‘ Build the format options string dynamically from the table and populate the dictionary
    formatOptions = “Please choose a format:” & vbCrLf
    For Each caseRow In caseTable.ListRows
    formatOptions = formatOptions & caseRow.Range.Cells(1, 1).Value & “. ” & caseRow.Range.Cells(1, 2).Value & vbCrLf
    caseDict(caseRow.Range.Cells(1, 1).Value) = caseRow.Range.Cells(1, 3).Value
    Next caseRow

    ‘ Check if the active cell has a spilled range
    If Not ActiveCell.HasSpill Then
    MsgBox “The active cell does not have a spilled range.”
    Exit Sub
    End If

    ‘ Get the spilled range
    Set spilledRange = ActiveCell.SpillingToRange

    ‘ Get the selected format type from the user
    formatType = InputBox(formatOptions, “Select Format”)

    ‘ Validate the user input and get the appropriate number format from the dictionary
    If caseDict.exists(Val(formatType)) Then
    numberFormat = caseDict(Val(formatType))
    Else
    MsgBox “Invalid selection. Defaulting to General.”
    numberFormat = “General”
    End If

    ‘ Apply the selected format to the spilled range
    spilledRange.numberFormat = numberFormat

    MsgBox “Formatting applied to the spilled range.”
    End Sub

    Kind Regards,

    Bhavik

    Reply

      • Neale’s comments are valid, however, I have encountered users who are apprehensive about using VBA and hesitant to modify format codes. The purpose of the table was to minimise the need for users to interact with the code.

        Reply