In case anyone would like to use some code similar to this, here is an example of the solution I ended up with, thanks to others smarter than I.
Sub CustomNumberFormat()
Dim Cell, rngX As Range
Dim strFormat As String
Dim intCol As Integer
Set rngX = Range("B3:Z3")
strSlsFormat = "$#,##0_);[Red]($#,##0)"
strNbrFormat = "#,##0_);[Red](#,##0)"
strPerFormat = "#,##0.0%_);[Red](#,##0.0%)"
strPriFormat = "$#,##0.00_);[Red]($#,##0.00)"
Application.ScreenUpdating = False
For Each Cell In rngX
Cell.Activate
Select Case True
Case (ActiveCell.Value Like "*Sales*")
intCol = ActiveCell.Column
With Columns(intCol)
.NumberFormat = strSlsFormat
End With
Case (ActiveCell.Value Like "*Qty*")
intCol = ActiveCell.Column
With Columns(intCol)
.NumberFormat = strNbrFormat
End With
Case (ActiveCell.Value Like "*%*")
intCol = ActiveCell.Column
With Columns(intCol)
.NumberFormat = strPerFormat
End With
Case (ActiveCell.Value Like "*Retail*")
intCol = ActiveCell.Column
With Columns(intCol)
.NumberFormat = strPriFormat
End With
Case Else
End Select
Next Cell
Range("A3").Select
Application.ScreenUpdating = True
End Sub