Hi
I am currently doing a vlookup and on the value returned I want the format to change. I want the cell to be formatted in thousands and also have the thousand separator if the actual figure is in millions.
e.g. 1234567 should be 1,235
-1234567 should be (1,235) in red

The code I am currently using is
Selection.NumberFormat = "0," (this returns 1235 or -1235)




Any ideas how to get it all?

You can use a custom number format:
0,0,;-0,0,[Red]
or:
#,#00,;[Red]-#,#00,

If you format the cells that way beforehand, there's no need to do it in code; otherwise, try:
Selection.NumberFormat = "0,0,;-0,0,[Red]"

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]