Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Custom Cell Format VIA VBA Macro

Posted on 2011-02-22
2
Medium Priority
?
900 Views
Last Modified: 2012-06-27
I'm currently using the following as a custom cell format:

"_($* #,##0.00_);[Red]_($* -#,##0.00_);_($* " - "??_);_(@_)"

Instead of right-clicking the cell and setting this format using the cell properties, I would like to set this via a VBA macro.

I have tried
Range("H:K").Select
Range("H:K").FormatConditions = "_($* #,##0.00_);[Red]_($* -#,##0.00_);_($* " - "??_);_(@_)"

Open in new window

or
Selection.NumberFormat = "_($* #,##0.00_);[Red]_($* -#,##0.00_);_($* " - "??_);_(@_)"

Open in new window


But that doesn't work.

Alternatively, I have also tried
Range("H:K").Select
Selection.Style = "Currency"

Open in new window

But that doesn't show negative numbers in red or show the negative sign (it formats with brackets).

Essentially, I would like to format (via vba macro) the range of cells as currency with 2 decimal places, $ sign, comma ($1000) separator and red if negative.

Thanks

0
Comment
Question by:bingie
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 2000 total points
ID: 34958502
Try:

Range("H:K").NumberFormat = "_($* #,##0.00_);[Red]_($* -#,##0.00_);_($* "" - ""??_);_(@_)"

Kevin
0
 
LVL 11

Author Closing Comment

by:bingie
ID: 34958517
Perfect!

Thanks Kevin!
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a serious pitfall that can happen when deleting shapes using VBA.
This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question