Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 184
  • Last Modified:

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.
0
dbfromnewjersey
Asked:
dbfromnewjersey
3 Solutions
 
unknown_routineCommented:
Here is the code:

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

Open in new window

0
 
Steven HarrisPresidentCommented:
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
 
dbfromnewjerseyAuthor Commented:
Thanks
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now