NumberFormat

[Forrest]

I want to conditionally format the cell. Positive results
should say $10gain and negative results say $10loss.
Creating a custom number format within Excel2002 works
fine, but copying into VBA creates an error. The following
is my vba code without the text.

With xlWs
.Cells(6, 2).NumberFormat = "$#,##0_);($#,##0)"
End With

Help?

 

[Tushar Mehta]

What happens if you turn on the macro recorder, create the custom
format and turn off the recorder? Doesn't XL give you the necessary
syntax?

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Forrest]

No. The macro recorder when adding text gives me something like this: $#,##0.0"m
When I incorporate that into my code I get: "Compile error: Expected : end of statement" The parenthesis are causing the error?

 

[Tushar Mehta]

Don't know what to write. Works for me. The macro recorder generates
the correct number of quotes

"_($* #,##0_)"" gain"";_($* (#,##0)"" loss"";_($* ""-""_);_(@_)"


--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Forrest]

Tushar, thank-you much your syntax worked it had to do
with the number of quotes.

Forrest

-----Original Message-----
Don't know what to write. Works for me. The macro recorder generates
the correct number of quotes

"_($* #,##0_)"" gain"";_($* (#,##0)"" loss"";_($* ""- ""_);_(@_)"


--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

(e-mail address removed) says...

error: Expected : end of statement" The parenthesis are
causing the error?