Fomat with Code Q

[Seanie]

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)

 

[Patrick Molloy]

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

 

[JLGWhiz]

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]

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.

 

[Seanie]

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

 

[JLGWhiz]

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