• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 910
  • Last Modified:

Custom Cell Format VIA VBA Macro

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
bingie
Asked:
bingie
1 Solution
 
zorvek (Kevin Jones)ConsultantCommented:
Try:

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

Kevin
0
 
bingieAuthor Commented:
Perfect!

Thanks Kevin!
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

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