I wish to format an existing sheet so that all negative values show in
red. This seems simple, but I have a few problems:-

1) Its a very large sheet and,
2) I can't format all ranges within a column as I have dates in some
rows which I wish to leave as is

How then could I format via code:-

So a negative value would shows as -1,234.56 (in red)
And a postive value would shows as 1,234.56 (in black)

go to the menu FORMAT / STYLES and with Normal in the style name, click Modify

in the modify form select the number tab, then set it as you want, click OK
then OK








"Seanie" wrote:

I wish to format an existing sheet so that all negative values show in
red. This seems simple, but I have a few problems:-

1) Its a very large sheet and,
2) I can't format all ranges within a column as I have dates in some
rows which I wish to leave as is

How then could I format via code:-

So a negative value would shows as -1,234.56 (in red)
And a postive value would shows as 1,234.56 (in black)

Sub dk()
For Each c In ActiveSheet.UsedRange.Cells
If c.Value < 0 Then
c.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
End If
Next
End Sub



"Seanie" wrote in message
...
I wish to format an existing sheet so that all negative values show in
red. This seems simple, but I have a few problems:-

1) Its a very large sheet and,
2) I can't format all ranges within a column as I have dates in some
rows which I wish to leave as is

How then could I format via code:-

So a negative value would shows as -1,234.56 (in red)
And a postive value would shows as 1,234.56 (in black)

Thanks, I'd do it that way, but I've 1500 rows of data over 25 columns
within which I have 35 rows of dates, which I don't want to touch, so
that is the main issue in doing a 'mass' format.

Thanks, quite an imaginative piece of code

This changes all values that are currently less than 1 to a minus red,
but how can I change even values that are positive to to show as
1,234.56 (in black) - I know theu won't change, but this way it helps
me maintaining my sheet going forward

Unless you specifically change them to another color, they should
automatically appear in black. Only the negative numbers should appear in
red.


"Seanie" wrote in message
...
Thanks, quite an imaginative piece of code

This changes all values that are currently less than 1 to a minus red,
but how can I change even values that are positive to to show as
1,234.56 (in black) - I know theu won't change, but this way it helps
me maintaining my sheet going forward