Format cells programmatically

I have a range of cells in an Excel worksheet, columns B through Z and rows 2 through 100. I need to set the format of each cell in that range to numeric, no decimals and negative numbers represented by parenthesis instead of a minus sign. How do I do it programmatically?  I already have a macro that does some other stuff. I just need to add a routine to it to accomplish what I just described. Thank you.
dbfromnewjerseyAsked:
Who is Participating?
 
Steven HarrisConnect With a Mentor PresidentCommented:
In line with the last question, and run the same way:

Sub ChangeNumberFormat()
Dim cell As Range
On Error Resume Next
    For Each cell In Selection
        cell.NumberFormat = "#;(#);0;@"
    Next
End Sub

Open in new window


or if you are wanting to combine the operations for each selection made:

Sub FillEmptyandChangeFormat()
Dim cell As Range
Dim InputValue As String
On Error Resume Next
InputValue = "0"
    For Each cell In Selection
        If IsEmpty(cell) Then
            cell.Value = InputValue
        End If
    Next
    For Each cell In Selection
        cell.NumberFormat = "#;(#);0;@"
    Next
End Sub

Open in new window

0
 
unknown_routineConnect With a Mentor Commented:
Here is the code:

Range("B2:Z100").Select
Selection.NumberFormat = "0_);(0)"
0
 
byundtConnect With a Mentor Commented:
It's a one-line macro:
Sub Formatter()
Range("B2:Z100").NumberFormat = "#;(#);0;@"
End Sub

Open in new window

0
 
dbfromnewjerseyAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.