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.
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.
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
Thanks for spending the time, writing and sharing your code Bhavik – that is pretty comprehensive.
Hopefully MS will update spilling to include the format so we don’t have to use VBA.
Thank you for sharing your ideas and tips too.
Thank you for sharing your ideas and tips too.
Always happy to share Excel techniques.
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
Hi Bhavik
Using a table in a specific file is flexible for that file, but a standalone solution is ultimately easier to implement.
Regards
Neale
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.
That’s the advantage with my code – they format the top left cell as normal and just run the macro.
Regards
Neale