Solved

Custom Cell Format VIA VBA Macro

Posted on 2011-02-22
2
898 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 500 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

630 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